Re: Postgres chooses slow query plan from time to time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Sep 14, 2021 at 5:15 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>
> Kristjan Mustkivi <sonicmonkey@xxxxxxxxx> 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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux