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