From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anna B. <terzi@bk.ru>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Index-only scan not working when IN clause has 2 or more values
Date: Fri, 25 Nov 2022 10:40:35 -0500
Message-ID: <4056383.1669390835@sss.pgh.pa.us> (raw)
In-Reply-To: <1669301102.998685549@f535.i.mail.ru>
References: <1669301102.998685549@f535.i.mail.ru>
=?UTF-8?B?QW5uYSBCLg==?= <terzi@bk.ru> writes:
> create index "ix-transaction-client-trans_dttm-division"
> on transaction (client_id,
> trans_dttm desc,
> division_code);
>
> EXPLAIN (ANALYZE, BUFFERS)
> select *
> from transaction
> where client_id = 123456
> and (trans_dttm between TO_DATE('01.01.2020', 'dd.mm.yyyy') and TO_DATE('31.12.2022', 'dd.mm.yyyy'))
> and (division_code in
> ('not_existing_code1', 'not_existing_code2'))
> order by trans_dttm desc
> limit 50 offset 0;
The reason you get a plan like this:
> " -> Index Scan using ""ix-transaction-client-trans_dttm-division"" on transaction (cost=0.57..8350814.66 rows=28072 width=2675) (actual time=703291.834..703291.835 rows=0 loops=1)"
> " Index Cond: ((client_id = '123456'::numeric) AND (trans_dttm >= to_date('01.01.2020'::text, 'dd.mm.yyyy'::text)) AND (trans_dttm <= to_date('31.12.2022'::text, 'dd.mm.yyyy'::text)))"
> " Filter: ((division_code)::text = ANY ('{not_existing_code1,not_existing_code2}'::text[]))"
is that if the =ANY clause were an index condition, it would result
in multiple scans of the index, therefore the output would (in all
probability) not be sorted in index order. To produce the demanded
result, the plan would have to read the entire index scan and sort
its output. The planner estimates that that would be slower than
what it has done here. In practice it looks like you're reading
the whole scan output anyway because there are less than 50
matching rows, but the planner didn't know that.
The problem with =ANY producing unordered output can be dodged if
the =ANY is on the first index column; but I suppose that does not
help you here, since making division_code the first index column
would defeat getting output that's sorted by trans_dttm anyway.
You might try making extended stats on these three columns to see
if that helps the planner to get a better rowcount estimate.
If it understood that there were fewer than 50 matching rows,
it might opt for the use-the-=ANY-and-sort plan type.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, terzi@bk.ru
Subject: Re: Index-only scan not working when IN clause has 2 or more values
In-Reply-To: <4056383.1669390835@sss.pgh.pa.us>
References: <1669301102.998685549@f535.i.mail.ru> <4056383.1669390835@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