| From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
|---|---|
| To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | [btree-indexed column] <@ [range | multirange] |
| Date: | 2023-06-08 10:05:44 |
| Message-ID: | f8a6cc72-e695-43fe-8857-7ca7c64da61c@app.fastmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
I've noticed the planner is not yet smart enough to do an index scan
when the left operand of a contains operator (<@) is a btree-indexed column
and the right operand is a range or multirange type of the same type
as the column.
For instance, given a users table with an id int primary key column,
these two queries are functionally equivalent, but only the second
one makes use of the btree index:
SELECT COUNT(*) FROM users WHERE id <@ int4range(10,20);
SELECT COUNT(*) FROM users WHERE id >= 10 AND id < 20;
Multirange example:
SELECT COUNT(*) FROM users WHERE id <@ int4multirange('{[10,20),[30,40)}');
SELECT COUNT(*) FROM users WHERE id >= 10 AND id < 20 OR id >= 30 AND id < 40;
I think support for this would open up for some interesting new use-cases,
when range/multirange could be used to store aggregated intermediate IDs
which would then be filtered on using a btree-indexed column.
/Joel
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Verite | 2023-06-08 10:08:12 | Re: Order changes in PG16 since ICU introduction |
| Previous Message | Hannu Krosing | 2023-06-08 10:04:05 | Re: Let's make PostgreSQL multi-threaded |