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.ja...@gmail.com> wrote: > > On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi <sonicmon...@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 -- Kristjan Mustkivi Email: kristjan.mustk...@gmail.com
