Re: [HACKERS] Highly Variable Planning Times

2017-04-20 Thread Greg Stark
On 19 April 2017 at 22:39, Michael Malis wrote: >> *At best*, you're doing substantial work in the >> planner to avoid the first tree descent step or two in a single >> non-partial index. Fwiw, in addition to replacing the first few levels of the descent with

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Michael Malis
Thanks for the help Andres. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Andres Freund
On 2017-04-19 16:43:07 -0700, Michael Malis wrote: > > The profile seems to confirm that this is largely due to cache misses. > > Can you elaborate? Cache misses of what? Why is the planning time so > variable? Postgres uses caches for a lot of metadata of tables, indexes, ... Some actions, like

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Michael Malis
> The profile seems to confirm that this is largely due to cache misses. Can you elaborate? Cache misses of what? Why is the planning time so variable? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Andres Freund
On 2017-04-19 14:14:54 -0700, Michael Malis wrote: > > Could you also get a profile using perf record -g? The strace isn't > > telling us all that much. > > I've attached the perf.data file from `perf record -g -p > `. I'm not that familiar with perf so if there is more > information you need

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Andres Freund
On 2017-04-19 14:14:54 -0700, Michael Malis wrote: > > Could you also get a profile using perf record -g? The strace isn't > > telling us all that much. > > I've attached the perf.data file from `perf record -g -p > `. I'm not that familiar with perf so if there is more > information you need

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Jeff Janes
On Wed, Apr 19, 2017 at 2:39 PM, Michael Malis wrote: > > TBH, I see no convincing explanation in that article of why 1300 partial > > indexes are a good idea. > > I don't like it much either. I've been trying to eliminate most of the > need for the partial indexes, but

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Michael Malis
> TBH, I see no convincing explanation in that article of why 1300 partial > indexes are a good idea. I don't like it much either. I've been trying to eliminate most of the need for the partial indexes, but this is the current state of things. > *At best*, you're doing substantial work in the >

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Tom Lane
Michael Malis writes: > I've been encountering highly variable planning time on PG 9.5.4. > Running `EXPLAIN SELECT * FROM events_1171738` will take anywhere from > 200ms to 4s. This likely has to do with the table having 1300 partial > indexes on it (for reasons

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Alvaro Herrera
Michael Malis wrote: > Hi, > > I've been encountering highly variable planning time on PG 9.5.4. > Running `EXPLAIN SELECT * FROM events_1171738` will take anywhere from > 200ms to 4s. This likely has to do with the table having 1300 partial > indexes on it (for reasons elaborated on in >

Re: [HACKERS] Highly Variable Planning Times

2017-04-19 Thread Andres Freund
Hi, On 2017-04-19 13:39:40 -0700, Michael Malis wrote: > I've been encountering highly variable planning time on PG 9.5.4. > Running `EXPLAIN SELECT * FROM events_1171738` will take anywhere from > 200ms to 4s. This likely has to do with the table having 1300 partial > indexes on it (for reasons