[btree-indexed column] <@ [range | multirange]

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

Browse pgsql-hackers by date

  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