Re: Postgres chooses slow query plan from time to time

On Tue, Sep 14, 2021 at 5:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com> writes:
> > -> Index Scan Backward using player_balance_history_idx2 on
> > mytable pbh (cost=0.70..21639.94 rows=3885 width=66) (actual
> > time=5934.153..5934.153 rows=1 loops=1)
> > Index Cond: ((cage_code = $3) AND (cage_player_id =
> > $2) AND (modified_time < $5))
> > Filter: (((product_code)::text = ($1)::text) AND
> > ((balance_type)::text = ($4)::text))
> > Rows Removed by Filter: 95589
> > Buffers: shared hit=7623 read=18217
>
> So indeed, the core issue is that that filter condition is very selective,
> and applying it after the index scan is expensive. Perhaps it would help
> to create an index that includes those columns along with cage_code and
> cage_player_id. (It's not clear whether to bother with modified_time in
> this specialized index, but if you do include it, it needs to be the last
> column since you're putting a non-equality condition on it.)
>
> regards, tom lane

But the Primary Key is defined as btree (cage_code, cage_player_id,
product_code, balance_type, version) so this should be exactly that
(apart from the extra "version" column). And the majority of the query
plans are using the PK with only a small number of cases going for the
IDX2 that is btree (cage_code, cage_player_id, modified_time). So I am
wondering how to make them not do that.

But perhaps the index bloat is indeed playing a part here as both the
PK and IDX2 have ~50% bloat ratio. I will try REINDEX-ing the table
although finding a good window to do it might require some time.

Best regards,

Kristjan