> On Feb 26, 2015, at 12:47 AM, Sreerama Manoj <manoj.sreerama973@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",SometimesI am creating Indexes to optimize them. But, I was not successful sometimes as even I create Index to
optimizethem, 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
hardwareconfiguration, and the planner impacting settings (which might be different on different hardware). To use an
indexthe db must load the index blocks from disk, search through them, find the reference it cares about, then load the
blockwith 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
differentsituations. The planner will also respond very differently with a limited set of test or debug data versus a
fullloaded 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
indexesare not always a good solution, especially in tables with high quantities of inserts. There are options to use
differentkinds of indexes, and to configure the planner to treat individual tables/columns differently than normal.
Unlessyou know what you are doing, these customizations should only be used to treat problems that arise in production
orin production like simulations.
The normal development process is to create your tables and indexes and get your application up and running. When you
startnoticing slowdowns, track them down and only make adjustments to the db when you are solving a real problem.
Neil