pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18036: Query planner chooses sub-optimal query path given enough IN tuples.
2+ messages / 2 participants
[nested] [flat]
* BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples.
@ 2023-07-26 04:34 PG Bug reporting form <noreply@postgresql.org>
2023-07-26 11:08 ` Re: BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples. Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-07-26 04:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: patrick@redbubble.com
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples.
2023-07-26 04:34 BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples. PG Bug reporting form <noreply@postgresql.org>
@ 2023-07-26 11:08 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 2+ messages in thread
From: Magnus Hagander @ 2023-07-26 11:08 UTC (permalink / raw)
To: patrick@redbubble.com; pgsql-bugs@lists.postgresql.org
On Wed, Jul 26, 2023 at 1:07 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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"))
...
What do you get if you try this query on PostgreSQL?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-07-26 11:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-26 04:34 BUG #18036: Query planner chooses sub-optimal query path given enough IN tuples. PG Bug reporting form <noreply@postgresql.org>
2023-07-26 11:08 ` Magnus Hagander <magnus@hagander.net>
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