Sample Header Ad - 728x90

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 values, 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 values 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 values, 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