Re: Slow planning time for simple query - Mailing list pgsql-general

From Jeremy Finzel
Subject Re: Slow planning time for simple query
Date
Msg-id CAMa1XUjkEK5Y=JED2wkXAZQe9qW=LF1=f0Lmb94KDEvVCFuS=Q@mail.gmail.com
Whole thread Raw
In response to Re: Slow planning time for simple query  (Jeremy Finzel <finzelj@gmail.com>)
List pgsql-general


On Wed, Jun 6, 2018 at 1:13 PM, Jeremy Finzel <finzelj@gmail.com> wrote:


On Wed, Jun 6, 2018 at 12:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeremy Finzel <finzelj@gmail.com> writes:
> 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);

Hm.  Is this the first query executed in a session?  If so maybe it's
got something to do with populating caches and other session spin-up
overhead.

Another theory is it's some sort of locking issue.  Turning on
log_lock_waits, and setting deadlock_timeout to say 100ms, would help
in investigating that.

                        regards, tom lane

I have run it over and over with no improvement in the planning time, so I don't thing it's first in session-related.  I can only make it faster with a pl function so far.

We have log_lock_waits on and nothing shows, and turning down deadlock_timeout also doesn't do anything.

Thanks,
Jeremy

Forgot to mention, this is running on this version:
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit


Thanks,
Jeremy

pgsql-general by date:

Previous
From: Jeremy Finzel
Date:
Subject: Re: Slow planning time for simple query
Next
From: Tom Lane
Date:
Subject: Re: Code of Conduct plan