Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select setseed(0.1);
create table test (r int8range);
insert into test 
select int8range(least(a,b),greatest(a,b),'[]') from (
select (random()*12345678911)::int8 a,(random()*12345678911)::int8 b
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678901,12345678913,'[]'));
insert into test 
select int8range(least(a,b),greatest(a,b),'[]') from (
select (random()*12345678911)::int8 a,(random()*12345678911)::int8 b
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678910,12345678912,'[]'));
insert into test 
select int8range(least(a,b),greatest(a,b),'[]') from (
select (random()*12345678911)::int8 a,(random()*12345678911)::int8 b
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678902 ,12345678920,'[]'));
insert into test 
select int8range(least(a,b),greatest(a,b),'[]') from (
select (random()*12345678911+12345678913)::int8 a,(random()*12345678911+12345678913)::int8 b
from generate_series(1,(select 5e4-count(*) from test)))_;

setseed

SELECT 1

CREATE TABLE

INSERT 0 110183

INSERT 0 1

INSERT 0 81493

INSERT 0 1

INSERT 0 57659

INSERT 0 1

INSERT 0 0

select count(*) from test;

count

249338

SELECT 1

create index on test using gist(r);

CREATE INDEX

vacuum analyze test;

VACUUM

explain analyze verbose
select * from test where r@>12345678912;

QUERY PLAN

Index Only Scan using test_r_idx on public.test  (cost=0.28..4.30 rows=1 width=22) (actual time=0.048..0.049 rows=3 loops=1)

  Output: r

  Index Cond: (test.r @> '12345678912'::bigint)

  Heap Fetches: 0

Planning Time: 1.274 ms

Execution Time: 0.083 ms

EXPLAIN

select * from test where r@>12345678912 limit 5;

r

[12345678901,12345678914)

[12345678910,12345678913)

[12345678902,12345678921)

SELECT 3