CODEWITHSUNDEEP
postgresql
Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Advice on createing index for combination of int. range + int |Postgres

I have a question on the index for the following table.

create table ascertain_telephonenumbersmodel
(

    id            serial                   not null
        constraint ascertain_telephonenumbersmodel_pkey
            primary key,

    abc_or_def    smallint                 not null
        constraint ascertain_telephonenumbersmodel_abc_or_def_check
            check (abc_or_def >= 0),

    numbers_range int4range                not null,

    volume        smallint                 not null
        constraint ascertain_telephonenumbersmodel_volume_check
            check (volume >= 0),

    operator      varchar(50)              not null,

    region        varchar(100)             not null,

    update_date   timestamp with time zone not null,

);

The only one type of query this table is dealing with is

select
       *
from
     ascertain_telephonenumbersmodel
where
      abc_or_def=`some integer` and numbers_range @> `some integer`
      # example  abc_or_def=900 and numbers_range @> 2685856

Question is – what is the best way of creating index for this condition?

DB – PostgreSQL 13

Number of rows ~ 400.000

Current execution time ~ 80-110 msec.

Thank you!

Upvotes: 0

Views: 309

Answers (1)

jjanes
jjanes

Reputation: 44147

A compound gist index. You will need to use an extension to include the int in the index.

create extension btree_gist;
create index on ascertain_telephonenumbersmodel using gist (abc_or_def, numbers_range);

Upvotes: 1

Related Questions