From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Wheeler <dwheeler@dgitsystems.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        Cameron Redpath <credpath@dgitsystems.com>
Subject: Re: Slow planning, fast execution for particular 3-table query
In-reply-to: <175BE386-F861-4064-8B5D-93E63BD90F13@dgitsystems.com>
References: <175BE386-F861-4064-8B5D-93E63BD90F13@dgitsystems.com>
Comments: In-reply-to David Wheeler <dwheeler@dgitsystems.com>
	message dated "Mon, 04 Nov 2019 03:04:45 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <15854.1572843659.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 04 Nov 2019 00:00:59 -0500
Message-ID: <15855.1572843659@sss.pgh.pa.us>
Precedence: bulk

David Wheeler <dwheeler@dgitsystems.com> writes:
> We’re having trouble working out why the planning time for this
> particular query is slow (~2.5s vs 0.9ms execution time). As you can see
> below, there are only 3 tables involved so it’s hard to imagine what
> decisions the planner has to make that take so long.

I wonder whether this traces to the cost of trying to estimate the
largest/smallest value of an indexed column by looking into the index.
Normally that's pretty cheap, but if you have a lot of recently-inserted
or recently-deleted values at the end of the index, it can get painful.
AFAIR this only happens for columns that are equijoin keys, so the fact
that your query is a join is significant.

I'm not convinced that this is the problem, because it's a corner case
that few people hit.  To see this issue, you have to have recently
inserted or deleted a bunch of extremal values of the indexed join-key
column.  And the problem only persists until those values become known
committed-good, or known dead-to-everybody.  (Maybe you've got a
long-running transaction somewhere, postponing the dead-to-everybody
condition?)

> Postgres version 9.5.19

If this *is* the cause, v11 and up have a performance improvement that
you need:

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3ca930fc3

			regards, tom lane