Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does Postgres choose index scan instead of index seek to fetch one record by primary key?

Tags:

sql

postgresql

I have the following table:

create table documents 
(
     id serial not null primary key,
     key varchar(50) not null,
     document jsonb
);

It has over 100M records and when I run a query to get 1 record by primary key:

 select * from documents where id = 20304050

It uses the index scan to get it:

Index Scan using documents_pkey on documents (cost=0.57..8.59 rows=1 width=533) (actual time=0.010..0.011 rows=0 loops=1)
  Index Cond: (id = 20304050)
Planning Time: 0.070 ms
Execution Time: 0.024 ms

Why does Postgres choose to use an index scan instead of an index seek?

Edit: I came from the SQL Server world where it was a distinction between an index scan and an index seek. In Postgres, there is no such thing as an index seek.

like image 997
syned Avatar asked Sep 13 '25 23:09

syned


1 Answers

This question has already been answered in the comments. „Index scan“ in postgres is the correct way of the query planner saying: „I‘m using an index to find that row(s)“. There is no concept of „index seek“ in postgres.

like image 56
Hechi Avatar answered Sep 15 '25 13:09

Hechi