From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: patrick@redbubble.com
Subject: BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples.
Date: Wed, 26 Jul 2023 04:34:29 +0000
Message-ID: <18036-f168e30b92352489@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18036
Logged by: Patrick Leamon
Email address: patrick@redbubble.com
PostgreSQL version: 14.3
Operating system: Unsure - AWS Aurora PostgreSQL
Description:
Hi,
I'm hitting some odd query plans. The structure of the query is like
this:
SELECT field1, field2, field3
FROM my_table
WHERE (field1, field2) IN (("a", "b"), ("c", "b"))
There is a composite index on field1 + field2 in that order.
When the query is working well, I end up with a query plan that looks
like:
BitmapOr
-> Bitmap Index Scan
-> Index Cond (field1="a" AND field2="b")
-> Bitmap Index Scan
-> Index Cond (field1="c" AND field2="d")
This is great and super fast.
When I give "too many" tuples in the IN clause, I end up with a query plan
like this:
BitmapAnd
BitmapOr
-> Bitmap Index Scan
-> Index Cond (field1="a" AND field2="b")
-> Bitmap Index Scan
-> Index Cond (field1="c" AND field2="b")
Bitmap Index Scan
-> Index Cond (field2="b")
This is not great and very slow.
The example above is simplified, in reality this is a 4 billion row table.
field 2 has only around 100 different values, where field 1 has millions.
So scanning the index on the "wrong side" of the composite index is very
costly and ends up timing out.
Over time the value of "too many" tuples is trending down. 100 used to be
fine, now that's having issues too.
Why would the query planner ever choose to scan a composite index on the
secondary column?
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, patrick@redbubble.com
Subject: Re: BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples.
In-Reply-To: <18036-f168e30b92352489@postgresql.org>
* 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