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
