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(a,(a+random()*1e4)::int8,'[]') from (
select (random()*2e10)::int8 a
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678901,12345678913,'[]'));
insert into test 
select int8range(a,(a+random()*1e4)::int8,'[]') from (
select (random()*2e10)::int8 a
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678910,12345678912,'[]'));
insert into test 
select int8range(a,(a+random()*1e4)::int8,'[]') from (
select (random()*2e10)::int8 a
from generate_series(1,(random()*15e4)::int))_;

insert into test values (int8range(12345678902 ,12345678920,'[]'));
insert into test 
select int8range(a,(a+random()*1e4)::int8,'[]') from (
select (random()*2e10)::int8 a
from generate_series(1,(select 5e5-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 250662

select count(*) from test;

count

500000

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.29..4.30 rows=1 width=22) (actual time=0.054..0.057 rows=3 loops=1)

  Output: r

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

  Heap Fetches: 0

Planning Time: 1.222 ms

Execution Time: 0.097 ms

EXPLAIN

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

r

[12345678901,12345678914)

[12345678910,12345678913)

[12345678902,12345678921)

SELECT 3