Re: Slow planning time for simple query

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Jeremy Finzel <finzelj(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Slow planning time for simple query
Date: 2018-06-06 22:13:08
Message-ID: 87efhjimx7.fsf@jsievers.enova.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>
>> 2018-06-06 18:59 GMT+02:00 Jeremy Finzel <finzelj(at)gmail(dot)com>:
>>> 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);
>
>> more times I seen similar issue based on bloated indexes on table - pg in
>> planning time detect min max from possible indexes
>
> Oh, hmm, yeah it could be ye olde get_actual_variable_range() issue.
> When this happens, are there perhaps a lot of recently-dead rows at either
> extreme of the range of table1.source_id or table2.id?

We noticed the cluster of interest had a rogue physical rep slot holding
71k WAL segments.

Dropping same slot seemed to correlate with the problem going away.

Does that sound like a plausible explanation for the observed slow
planning times?

Thx

> We made a fix last year to improve that:
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3ca930fc3
> but it wasn't back-patched.
>
> regards, tom lane
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-06-06 22:51:58 Re: Which backend using which pg_temp_N schema?
Previous Message Jerry Sievers 2018-06-06 21:00:28 Re: Which backend using which pg_temp_N schema?