Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgres chooses slow query plan from time to time - Mailing list pgsql-performance

From Jeff Janes
Subject Re: Postgres chooses slow query plan from time to time
Date
Msg-id CAMkU=1x_9g7BZrPwpaVhwvVe7DKTR9gTgGLCtyVQq_m0iBGvRw@mail.gmail.com
Whole thread Raw
In response to Postgres chooses slow query plan from time to time  (Kristjan Mustkivi <sonicmonkey@gmail.com>)
Responses Re: Postgres chooses slow query plan from time to time  (Kristjan Mustkivi <sonicmonkey@gmail.com>)
List pgsql-performance
Tree view
On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi <sonicmonkey@gmail.com> wrote:
 
SELECT
    *
FROM
    myschema.mytable pbh
WHERE
    pbh.product_code = $1
    AND pbh.cage_player_id = $2
    AND pbh.cage_code = $3
    AND balance_type = $4
    AND pbh.modified_time < $5
ORDER BY
    pbh.modified_time DESC FETCH FIRST 1 ROWS ONLY;
 
    "mytable_idx2" btree (cage_code, cage_player_id, modified_time)

Why does this index exist?  It seems rather specialized, but what is it specialized for?

If you are into specialized indexes, the ideal index for this query would be:
 
btree (cage_code, cage_player_id, product_code, balance_type, modified_time)

But the first 4 columns can appear in any order if that helps you combine indexes.  If this index existed, then it wouldn't have to choose between two other suboptimal indexes, and so would be unlikely to choose incorrectly between them.

Cheers,

Jeff

pgsql-performance by date:

Previous
From: "ldh@laurent-hasson.com"
Date:
Subject: RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Next
From: Jeff Janes
Date:
Subject: Re: Postgres chooses slow query plan from time to time