From: Josh <postgres@sirjosh3917.com>
To: pgsql-performance@postgresql.org
Subject: Missed query planner optimization: `n in (select q)` -> `n in (q)`
Date: Sat, 11 Jun 2022 23:50:49 +0000
Message-ID: <d502b53a497ec80e5d1fcd18e353e7e9@sirjosh3917.com> (raw)
Hey y'all!
So recently, I ran into an issue where a query I wrote wasn't using an index, presumably because what I was doing was too hard for the query planner to figure out. I've distilled the problem into its essence to the best of my ability, and found that it's because `select` seems to hinder it.
The problem boils down to the planner not figuring out that these two queries should use an index:
```sql
-- Setup
create table numbers(n int);
insert into numbers (n) select generate_series(1, 1000000);
create index numbers_n_idx on numbers(n);
-- Non-indexed queries
explain analyze select numbers.n from (values (5000000)) quantities(q)
join numbers on numbers.n in (select q);
explain analyze select numbers.n from (values (5000000)) quantities(q)
join numbers on numbers.n = any(select q);
```
These examples may seem silly, so let me provide a "case study" query that should justify the need for such an optimization. I had a query that was generating an array of items, and wanted to join it to a table given that some column of that table was present in the array. It looked like so:
```sql
select numbers.n from quantities join numbers on numbers.n in (select unnest(quantities.q));
```
This turned out to be horrendously slow, because it was performing a sequential scan! I did however end up settling on the following form:
```sql
select numbers.n from quantities join numbers on numbers.n = any(quantities.q);
```
This was only possible because I was dealing with arrays though, and an operation such as `in (select unnest...)` can be easily converted to `= any(...)`. However for the general case, I believe an optimization in this area may provide benefit as there may exist a circumstance that does not have an alternative to a sub-query select (`= any()` was my alternative), but I am just a database newbie.
I've noticed this problem has been around since at least 11.7, and is still present as of the `postgres:15beta1` docker image. I've attached a script which reproduces the issue. It uses docker, so I'm confident you'll be able to run it without issue.
Finally, I ask:
- Is this an issue that should be fixed? I'm a database newbie so I have no idea about the deep semantics of SQL and what a select inside a `join_condition` could imply to the planner to prevent it from optimizing it.
- If "yes" to the previous question, what would be the precise semantics of such an optimization? I loosely say `n in (select q)` -> `n in (q)` for all n and q, but of course I don't have enough knowledge to know that this is correct in terms of whatever Postgres' internal query optimization IR is.
- Can a database newbie like myself contribute an optimization pass in Postgres to fix this? I'm fascinated by the work y'all do, and submitting a patch to Postgres that makes it into production would make my week.
Thank you for your time, and have a great day!
Attachments:
[application/octet-stream] repro.sh (1.4K, ../d502b53a497ec80e5d1fcd18e353e7e9@sirjosh3917.com/2-repro.sh)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgres@sirjosh3917.com
Subject: Re: Missed query planner optimization: `n in (select q)` -> `n in (q)`
In-Reply-To: <d502b53a497ec80e5d1fcd18e353e7e9@sirjosh3917.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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