Re: temporary indexes?

On Oct 22, 2015, at 2:08 PM, Tom Lane wrote:
> FWIW, I don't find much attraction in the idea of building an index for
> use by a single query. There basically isn't any scenario where that's
> going to beat running a plan that doesn't require the index. The value of
> an index is generally to avoid a whole-table scan and/or a sort, but
> you'll necessarily pay those costs to make the index.

On Oct 22, 2015, at 8:17 AM, vincent elschot wrote:
> Do you mean creating a temporary index on a non-temporary table to speed up the queries that fills the temporary table?

One of the use-cases is speeding up inserts on create, but another is for periodic analytics routines (which we handle with explicit create/drop index commands.

In one example of our analytics routines, we end up needing to create/drop about 15 indexes to optimize 45 queries. This speeds up the execution by 1000% and minimizes RAM usage. We don't keep the indexes active, because we only need them for analytics and the overhead of managing them during high write periods during the day is noticeable. Creating and dropping these indexes on-demand gives us all the benefit with none of the drawbacks.