pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
4+ messages / 3 participants
[nested] [flat]

* Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
@ 2024-03-09 19:08 Ilya Basin <basinilya@gmail.com>
  2024-03-10 11:51 ` 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-10 11:58 ` Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` Thomas Kellerer <shammat@gmx.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Ilya Basin @ 2024-03-09 19:08 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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?






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
  2024-03-09 19:08 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-10 11:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 4+ messages in thread

From: Laurenz Albe @ 2024-03-10 11:51 UTC (permalink / raw)
  To: Ilya Basin <basinilya@gmail.com>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
  2024-03-09 19:08 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-10 11:58 ` Thomas Kellerer <shammat@gmx.net>
  2024-03-10 20:51   ` 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>
  1 sibling, 1 reply; 4+ messages in thread

From: Thomas Kellerer @ 2024-03-10 11:58 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`
  2024-03-09 19:08 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-10 11:58 ` Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` Thomas Kellerer <shammat@gmx.net>
@ 2024-03-10 20:51   ` Ilya Basin <basinilya@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Ilya Basin @ 2024-03-10 20:51 UTC (permalink / raw)
  To: Thomas Kellerer <shammat@gmx.net>; laurenz.albe@cybertec.at; +Cc: pgsql-general@lists.postgresql.org

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









^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2024-03-10 20:51 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-09 19:08 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-10 11:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-10 11:58 ` Thomas Kellerer <shammat@gmx.net>
2024-03-10 20:51   ` Ilya Basin <basinilya@gmail.com>

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