Re: Postgres chooses slow query plan from time to time

On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi <sonicmonkey(at)gmail(dot)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