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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>> 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.stehule@xxxxxxxxx>
Date: Monday, 4 November 2019 at 4:53 pm
To: David Wheeler <dwheeler@xxxxxxxxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>, "pgsql-performance@xxxxxxxxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>, Cameron Redpath <credpath@xxxxxxxxxxxxxxx>
Subject: Re: Slow planning, fast execution for particular 3-table query

 

 

 

po 4. 11. 2019 v 6:17 odesílatel David Wheeler <dwheeler@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:

    David Wheeler <dwheeler@xxxxxxxxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux