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(?)



Reply via email to