Does Postgres use index on BIGINT columns?

Question

I'm trying to understand whether Postgres uses indexes on BIGINT columns. The ddl to create the table and the corresponding index works. But when searching via Google, there were some articles (quite old) that said that there are some issues with BIGINT column indexes (they're not used). Unfortunately, I don't have enough sample data available to verify this using EXPLAIN.

create table ab.sample ( id integer primary key generated always as identity, some_id bigint ); create unique index sample_some_id_idx on ab.sample(some_id);

There is no bias against indexes on bigint columns. You might share the EXPLAIN (ANALYZE, BUFFERS) output for a more detailed analysis. — Laurenz Albe
– Laurenz Albe, Commented Jan 28, 2020 at 7:25

Hambone · Accepted Answer · 2020-01-28 03:52:54Z

I would say under the right conditions (namely sufficient selectivity warranting an index scan), definitely... what version are you on?

insert into sample (some_id) select generate_series(1,1000000); analyze sample; explain select * from sample where some_id between 500000 and 500010;

Results:

Index Scan using sample_some_id_idx on sample (cost=0.42..2.84 rows=11 width=12) Index Cond: ((some_id >= 500000) AND (some_id <= 500010))

n0099 · Accepted Answer · 2024-05-18 13:30:12Z

https://dba.stackexchange.com/questions/146294/why-does-postgresql-perform-a-seq-scan-when-comparing-a-numeric-value-with-a-big/339617#339617

Make sure to either

bind parameter some_id as bigint in prepared statement
cast any number literal to bigint manually like some_id = 1234::bigint

Note even with prepared statement managed by ORM, if some_id is mapping to ulong type from the view of ORM, it will still cause implicitly ::numeric casting which will bypass index on the bigint column since there's no unsigned types in Postgres: Why unsigned integer is not available in PostgreSQL?

FYI:

CREATE TABLE test(num int not null, PRIMARY KEY (num)); INSERT INTO test SELECT * FROM generate_series(0, 32672); EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT * FROM test WHERE num = 9223372036854775807;

Index Only Scan using test_pkey on tbm.test (cost=0.29..1.41 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1) Output: num Index Cond: (test.num = '9223372036854775807'::bigint) Heap Fetches: 0 Buffers: shared hit=2 Query Identifier: 3505370066652848307 Planning Time: 0.052 ms Execution Time: 0.024 ms

EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT * FROM test WHERE num = 9223372036854775808;

Seq Scan on tbm.test (cost=0.00..635.10 rows=163 width=4) (actual time=3.427..3.427 rows=0 loops=1) Output: num Filter: ((test.num)::numeric = '9223372036854775808'::numeric) Rows Removed by Filter: 32673 Buffers: shared hit=145 Query Identifier: 7606985776533665305 Planning Time: 0.055 ms Execution Time: 3.439 ms

CREATE TABLE test(num smallint not null, PRIMARY KEY (num)); INSERT INTO test SELECT * FROM generate_series(0, 32672); EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT * FROM test WHERE num = 32672;

Index Only Scan using test_pkey on tbm.test (cost=0.29..2.51 rows=1 width=2) (actual time=0.018..0.018 rows=1 loops=1) Output: num Index Cond: (test.num = 32672) Heap Fetches: 1 Buffers: shared hit=4 Query Identifier: -4575445827832434577 Planning: Buffers: shared hit=15 Planning Time: 0.091 ms Execution Time: 0.029 ms

EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT * FROM test WHERE num = 2147483647;

Index Only Scan using test_pkey on tbm.test (cost=0.29..2.51 rows=1 width=2) (actual time=0.014..0.014 rows=0 loops=1) Output: num Index Cond: (test.num = 2147483647) Heap Fetches: 0 Buffers: shared hit=2 Query Identifier: -4575445827832434577 Planning Time: 0.063 ms Execution Time: 0.032 ms

EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT * FROM test WHERE num = 4294967296;

Index Only Scan using test_pkey on tbm.test (cost=0.29..2.51 rows=1 width=2) (actual time=0.007..0.007 rows=0 loops=1) Output: num Index Cond: (test.num = '4294967296'::bigint) Heap Fetches: 0 Buffers: shared hit=2 Query Identifier: 4896689328011101216 Planning Time: 0.029 ms Execution Time: 0.016 ms

Collectives™ on Stack Overflow

Does Postgres use index on BIGINT columns?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related