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@xxxxxxxxx> wrote: > > On Mon, Sep 13, 2021 at 9:25 AM Kristjan Mustkivi <sonicmonkey@xxxxxxxxx> 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.mustkivi@xxxxxxxxx
