Re: Slow planning, fast execution for particular 3-table query

> 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?)

There are no long-running transactions that have backend_xmin set in pg_stat_activity, if that's what you mean here. There are also no open prepared transactions or replication slots which I understand have a similar keeping-things-alive issue.

These tables are biggish (hundreds of mb), but not changing so frequently that I'd expect large quantities of data to be inserted or deleted before autovac can get in there and clean it up. And certainly not in a single uncommitted transaction.

I'll try reindexing each of the tables just to make sure it's not strange index imbalance or something causing the issue.

Regards,

David

On 4/11/19, 4:01 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

David Wheeler <dwheeler(at)dgitsystems(dot)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