Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
Hi List. I have a list of bigint keys and I need to retrieve rows by these keys. Normally, I would split this list into pages of size 900 and perform several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we use can only produce this SQL: `SELECT ... WHERE (key=$1 or key=$2 or ...)`. Surprisingly, PostgreSQL planner treats these two SQLs differently: - ```select * FROM "audittrail$referencelogline" where id in ( 1 , 2 , 3 )``` Index Scan https://i.stack.imgur.com/dr8oz.png - ```select * FROM "audittrail$referencelogline" where id = 1 or id = 2 or id = 3``` A lot of "Bitmap Index Scan" for each value https://i.stack.imgur.com/dnErs.png Is it possible to configure PostgreSQL 12.16 to treat the second query as the first?
Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
Laurenz thanks for the info. Thomas no I can't. Original Message From: Thomas Kellerer [mailto:sham...@gmx.net] Sent: Sunday, March 10, 2024 at 11:58 UTC To: pgsql-general@lists.postgresql.org Subject: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` Ilya Basin schrieb am 09.03.2024 um 20:08: Hi List. I have a list of bigint keys and I need to retrieve rows by these keys. Normally, I would split this list into pages of size 900 and perform several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we use can only produce this SQL: `SELECT ... WHERE (key=$1 or key=$2 or ...)`. Surprisingly, PostgreSQL planner treats these two SQLs differently: - ```select * FROM "audittrail$referencelogline" where id in ( 1 , 2 , 3 )``` Index Scan https://i.stack.imgur.com/dr8oz.png - ```select * FROM "audittrail$referencelogline" where id = 1 or id = 2 or id = 3``` A lot of "Bitmap Index Scan" for each value https://i.stack.imgur.com/dnErs.png Is it possible to configure PostgreSQL 12.16 to treat the second query as the first? Can you convince your obfuscation layer to send an array value (containing all IDs) and change the query to: select * FROM "audittrail$referencelogline" where id = any(?)
Re: subquery plan rows = 1, but it's merge joined instead of index lookup
Yes! "set join_collapse_limit = 9" was enough to fix the plan, thanks Laurenz. I will set both to 11. Original Message From: Laurenz Albe [mailto:laurenz.a...@cybertec.at] Sent: Thursday, April 11, 2024 at 14:21 UTC To: ilya Basin; pgsql-general@lists.postgresql.org Subject: subquery plan rows = 1, but it's merge joined instead of index lookup On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote: Is there some complexity limit after which the planner starts acting dumb? Yes, "join_collapse_limit" and "from_collapse_limit". You can try increasing them. Yours, Laurenz Albe
