From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
cc: Jeff Janes <jeff.janes@gmail.com>,
        Tomas Vondra <tomas.vondra@enterprisedb.com>,
        Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres chooses slow query plan from time to time
In-reply-to: 
 <CAOQPKati1jk6PniYNcodoC6T=4+ZrWxnmoVHOJe92-BpMsRHmg@mail.gmail.com>
References: 
 <CAOQPKavhdTrgQo8Uss0M+K0Du+_WYmdoGyMpC0hJQkKM+-X25Q@mail.gmail.com>
 <31c01752-c4d6-7751-9d25-b1fac8f3a7aa@enterprisedb.com>
 <CAOQPKasCkSAW6FTt_iCY1Ts8tUgT+mf+jro46QXE50eVAQ6Pvg@mail.gmail.com>
 <CAMkU=1zp56=kfsQMxfrEyVn4sxOY1HH3GPoANN5rGKUHvW4S7w@mail.gmail.com>
 <CAOQPKati1jk6PniYNcodoC6T=4+ZrWxnmoVHOJe92-BpMsRHmg@mail.gmail.com>
Comments: In-reply-to Kristjan Mustkivi <sonicmonkey@gmail.com>
	message dated "Tue, 14 Sep 2021 15:41:54 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <589349.1631628930.1@sss.pgh.pa.us>
Date: Tue, 14 Sep 2021 10:15:30 -0400
Message-ID: <589350.1631628930@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/589350.1631628930%40sss.pgh.pa.us>
Precedence: bulk

Kristjan Mustkivi <sonicmonkey@gmail.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