From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Michael Malis <michaelmalis2(at)gmail(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Highly Variable Planning Times |
Date: | 2017-04-19 20:47:14 |
Message-ID: | 20170419204714.hjy3mmzstghverc3@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2017-04-19 20:49:45 | Re: Highly Variable Planning Times |
Previous Message | Michael Malis | 2017-04-19 20:39:40 | Highly Variable Planning Times |