The system can’t know what conditions will be present when your query executes sometime in the future without defining those conditions. To define those conditions you create a simulated environment with the index, data, and load you want to test and test it.
Without more info, your requirements and constraints don’t make any sense. So, no there is no magic wand that can be waved to indicate whether or not your query will magically be faster or slower at some time in the future, with some unknown data, under some unknown db load. (I know that’s not what you intended to ask, but it is not clear why you are asking what you are asking.) If you are working on a live production system (which I do understand your reluctance to create indexes for testing) , then you would be better off creating a duplicate system and testing the queries and indexes on it. If you are running on a development system with only sample data, then the analyzer won’t give you good info anyway. Why not just create the indexes/data and evaluate the result in a simulated system? If you read and have a little understanding how the planner works and how statistics are generated, you can get usable info about indexes and relative performance in pretty simple simulated environments. Now, i’m just guessing, but you may be thinking that this simulation business is a lot of work. You could be right, which is why optimization often occurs after specific problems are identified rather than as an earlier intellectual exercise. Most people would just use general rules of thumb or their experience to create indexes until specific performance indicated something else is needed. Then they would solve the specific performance issue. Neil > On Feb 26, 2015, at 8:14 AM, Sreerama Manoj <manoj.sreerama...@gmail.com> > wrote: > > Hi, Neil > > Thanks for your reply...I understood that it the planner chooses > index based on the whether it increases performance or not...But, as part of > my work I need to know whether an Index Increases or decreases the > performance and the execution time of query before actually creating that > Index. Is there any provision to do that in Postgres (or) suggest any way to > find that > > On Thu, Feb 26, 2015 at 7:09 PM, Neil Tiffin <ne...@neiltiffin.com > <mailto:ne...@neiltiffin.com>> wrote: > > > On Feb 26, 2015, at 12:47 AM, Sreerama Manoj <manoj.sreerama...@gmail.com > > <mailto:manoj.sreerama...@gmail.com>> wrote: > > > > Hi, > > I use Postgres 9.4 database.Now,I am optimizing the queries by using > > the results of "explain" and "explain analyze",Sometimes I am creating > > Indexes to optimize them. But, I was not successful sometimes as even I > > create Index to optimize them, the planner is not using them . > > > > That is correct. The planner makes it decisions based on the availability of > indexes, the nature of your data, the hardware configuration, and the planner > impacting settings (which might be different on different hardware). To use > an index the db must load the index blocks from disk, search through them, > find the reference it cares about, then load the block with the data from > disk. Sometimes it is quicker to load the data block from disk and use it > directly without the index. You need to read the parts in the documentation > about using the planner. It is very configurable to handle many different > situations. The planner will also respond very differently with a limited > set of test or debug data versus a full loaded production data base as it is > designed to adapt to your data and the db load. > > Keep in mind that indexes will always slow down inserting and may or may not > increase the speed of retrieval. So indexes are not always a good solution, > especially in tables with high quantities of inserts. There are options to > use different kinds of indexes, and to configure the planner to treat > individual tables/columns differently than normal. Unless you know what you > are doing, these customizations should only be used to treat problems that > arise in production or in production like simulations. > > The normal development process is to create your tables and indexes and get > your application up and running. When you start noticing slowdowns, track > them down and only make adjustments to the db when you are solving a real > problem. > > Neil >
