po 4. 11. 2019 v 6:17 odesílatel David Wheeler <dwhee...@dgitsystems.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" <t...@sss.pgh.pa.us> wrote: > > David Wheeler <dwhee...@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 > > >
