Re: Slow planning time for simple query

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2018-06-06 18:59 GMT+02:00 Jeremy Finzel <finzelj@xxxxxxxxx>:

Hello - 

We have an odd scenario on one of our OLTP systems, which behaves the same way on a streamer, of a 700-1000ms planning time for a query like this:

SELECT *

FROM table1

WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);

The actual execution time is sub-ms.

We initially thought maybe catalog bloat?  But we were able to reindex all of the highly churned catalog tables, and I even did VACUUM FULL on pg_attribute and pg_statistic, to no avail.

There are no custom settings for pg_attribute for the given tables either.

Interestingly, the problem goes away on a SAN snapshot of the target system.

Any ideas of what else we could try?  A PL function that caches the query plan works, but that is just a workaround.

more times I seen similar issue based on bloated indexes on table - pg in planning time detect min max from possible indexes

Regards

Pavel

Thanks!

Jeremy