| From: | Ilya Basin <basinilya(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` |
| Date: | 2024-03-09 19:08:03 |
| Message-ID: | 0cf29b7e-3e9e-4698-81e3-aad5bb37f446@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Steve Baldwin | 2024-03-09 21:05:44 | Re: Help diagnosing replication (copy) error |
| Previous Message | hassan rafi | 2024-03-09 18:57:12 | Re: Seeing high query planning time on Azure Postgres Single Server version 11. |