Re: temporary indexes?

On Oct 22, 2015, at 5:04 PM, Jim Nasby wrote:

>
> 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.

Running without the indexes would take over an hour to execute the scripts, and totally jams the machine (we got 30minutes in once, and had to kill it). That's because of millions of rows used in joins and sequential scans.

Building all the indexes takes 30 seconds; most SQL commands then run only against the indexes (some of which are partial) and the entire suite finishes in about 3 minutes.

If the indexes stay active during the day, there seems to be a 2-3% drop in write performance. This is on a webapp, so we're just happier shifting the index work from peak hours to offpeak hours. It means we can delay spinning up another application server a bit longer.

I'll definitely look into your suggestions the next time I hit this code.