Thread
-
Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
Ilya Basin <basinilya@gmail.com> — 2024-03-09T19:08:03Z
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 Albe <laurenz.albe@cybertec.at> — 2024-03-10T11:51:31Z
On Sat, 2024-03-09 at 23:08 +0400, Ilya Basin wrote: > 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: > > Is it possible to configure PostgreSQL 12.16 to treat the second query as the first? No, that is currently not possible. Yours, Laurenz Albe
-
Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
shammat@gmx.net — 2024-03-10T11:58:25Z
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: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
Ilya Basin <basinilya@gmail.com> — 2024-03-10T20:51:21Z
Laurenz thanks for the info. Thomas no I can't. -------- Original Message -------- From: Thomas Kellerer [mailto:shammat@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(?)