Re: Slow planning time for simple query

On Wed, Jun 6, 2018 at 1:13 PM, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:

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