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
withexplicit 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.
Thisspeeds up the execution by 1000% and minimizes RAM usage.  We don't keep the indexes active, because we only need
themfor analytics and the overhead of managing them during high write periods during the day is noticeable.  Creating
anddropping these indexes on-demand gives us all the benefit with none of the drawbacks.