Check int4range includes number using B-tree index
1
vote
1
answer
293
views
I have a table with two important columns:
value
and m_range
, where value
:
CREATE TABLE m_filter (
value BIGINT NOT NULL,
m_range int4range NOT NULL,
EXCLUDE USING GIST (m_range WITH &&, value WITH =)
);
The usage scenario is following:
1. It's not frequently updated, but it can contain a quite large amount of data.
2. It's queries very frequently by searching for value
s, which are in m_range
.
So I'm supposing to use BTree
index for m_range
column instead of Gist
for better query performance:
CREATE INDEX i_m_filter_range ON m_filter USING BTREE (m_range);
But according to docs https://www.postgresql.org/docs/current/indexes-types.html BTree indexes supports only these operators:
= >
.
Is it possible to select all value
s where int4range
columnt contains some integer
using BTree? Like I can do with ranges using @>
and <@
operators.
---
Update:
In this table m_range
values could be int values from 0 to 9999, each value
has about 500 non-overlaping m_range
records average, and there are about 1_000 value
s, so it's about 500_000 m_range
values.
PostgreSQL version is 13.5
Asked by g4s8
(111 rep)
Jun 16, 2022, 04:16 PM
Last activity: Jun 24, 2022, 03:15 AM
Last activity: Jun 24, 2022, 03:15 AM