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

0

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))

answered Jan 28, 2020 at 3:52

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related