Re: temporary indexes?

On 10/22/15 3:15 PM, Jonathan Vanasco wrote:
> 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.

What % of execution time is spent creating those indexes? Or is that
factored into the 1000%? Also, could your analysis queries be run in a
REPEATABLE READ transaction (meaning that once the transaction starts it
doesn't get any new data)? If it could then the temp indexes could be
static, which would mean no update overhead.

Tom's point about unlogged indexes is a good one, I hadn't thought about
that.

Something else to think about here is catalog churn. So the ideal case
for an index that couldn't be static would be an unlogged index that you
could invalidate and rebuild at will. When you're done with it you
invalidate it and maintenance costs go to essentially 0. Rebuilds and
maintenance are cheaper because they're not WAL logged.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com