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 elaborated on in > https://blog.heapanalytics.com/running-10-million-postgresql-indexes-in-production/). > I'm trying to figure out if there is something I can do to eliminate > the slow planning from happening.
I'd suspect that that's triggered by cache rebuilds. If there's any concurrent relcache invalidation (e.g. by a concurrent vacuum, create index, alter table, relation extension, ...), a lot of metadata for all those indexes will have to be rebuilt. TBH, I don't think we're particularly likely to optimize hugely for workloads with 1300 indexes on individual tables - such an effort seems not unlikely to hurt more common cases. > When I used `strace -c` on the backend process, I found that the > number of `semop` system calls is much higher for the slower queries > than the faster ones. After digging in a bit more I found that when > the query was slow, there would be a single semaphore which would make > up most of the `semop` calls. Here is a snippet of the output when I > ran `strace -r` on a query with high planning time: Could you also get a profile using perf record -g? The strace isn't telling us all that much. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
