Re: Slow planning time for simple query

On 06/06/2018 09:59 AM, Jeremy Finzel wrote:
> 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.

I am trying to sort out the various references to times so could you:

Show the EXPLAIN ANALYZE?

and ?

psql> \timing

psql> SELECT *
FROM table1
WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);


> 
> 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.
> 
> Thanks!
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com