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
