Re: Postgres chooses slow query plan from time to time

Hi Jeff,

The specialized index is present due to some other queries and the
index is used frequently (according to the statistics). I do agree
that in this particular case the index btree (cage_code,
cage_player_id, product_code, balance_type, modified_time) would solve
the problem but at the moment it is not possible to change that
without unexpected consequences (this odd behavior manifests only in
one of our sites).

I will try if more aggressive autovacuum analyze will alleviate the
case as Tomas Vondra suggested.

Thank you for the help!

Kristjan

On Mon, Sep 13, 2021 at 10:21 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
> 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

--
Kristjan Mustkivi

Email: kristjan(dot)mustkivi(at)gmail(dot)com