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: | Raw Message | Whole Thread | 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 |