From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres chooses slow query plan from time to time
Date: Tue, 14 Sep 2021 10:15:30 -0400
Message-ID: <589350.1631628930@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sonicmonkey@gmail.com, jeff.janes@gmail.com, tomas.vondra@enterprisedb.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres chooses slow query plan from time to time
In-Reply-To: <589350.1631628930@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox