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.janes@gmail.com> wrote:
>
> 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'thave to choose between two other suboptimal indexes, and so would be unlikely to choose incorrectly between
them.
>
> Cheers,
>
> Jeff
--
Kristjan Mustkivi
Email: kristjan.mustkivi@gmail.com