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>
Cc: Cameron Redpath <credpath@dgitsystems.com>
Subject: Re: Slow planning, fast execution for particular 3-table query
Date: Mon, 04 Nov 2019 00:00:59 -0500
Message-ID: <15855.1572843659@sss.pgh.pa.us> (raw)
In-Reply-To: <175BE386-F861-4064-8B5D-93E63BD90F13@dgitsystems.com>
References: <175BE386-F861-4064-8B5D-93E63BD90F13@dgitsystems.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dwheeler@dgitsystems.com, pgsql-performance@lists.postgresql.org, credpath@dgitsystems.com
Subject: Re: Slow planning, fast execution for particular 3-table query
In-Reply-To: <15855.1572843659@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox