Re: Create Virtual Indexes on Postgres

> On Feb 26, 2015, at 12:47 AM, Sreerama Manoj <manoj(dot)sreerama973(at)gmail(dot)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