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

>> I'll try reindexing each of the tables just to make sure it's not strange index imbalance or something causing the issue.
> I seen this issue few time - and reindex helps.

Alas our reindex doesn’t seem to have helped. I’m going to see if we can reproduce this on a non-prod environment so we can muck about a bit more. If we can reproduce it in a safe place, is there a tool we can use to get more info out of the query planner to find what it’s doing to take so long?

Regards,

David

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Date: Monday, 4 November 2019 at 4:53 pm
To: David Wheeler <dwheeler(at)dgitsystems(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>, Cameron Redpath <credpath(at)dgitsystems(dot)com>
Subject: Re: Slow planning, fast execution for particular 3-table query

po 4. 11. 2019 v 6:17 odesílatel David Wheeler <dwheeler(at)dgitsystems(dot)com<mailto:dwheeler(at)dgitsystems(dot)com>> napsal:
> 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.

I seen this issue few time - and reindex helps.

Pavel

Regards,

David

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

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