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

Question

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.

There is no such thing as an "index seek" in Postgres - what do you mean with that? Plus: an index scan is the right thing for the optimizer to choose here. — user330315
– user330315, Commented Jul 19, 2019 at 7:25
@a_horse_with_no_name does postgres scans all pages of the index or it uses btree to find the page with the right id? — syned
– syned, Commented Jul 19, 2019 at 7:32
No it does not, it's a btree and it uses a "binary search" to find that row. If you run explain (analyze, buffers) you can actually see how many blocks it needs to find that row. — user330315
– user330315, Commented Jul 19, 2019 at 7:33
@a_horse_with_no_name ok, got it, thanks. I was confused by the word index scan. — syned
– syned, Commented Jul 19, 2019 at 7:35
To clarify: Some database systems, such as MS SQL Server, use the terms "index scan" and "index seek" to distinguish whether the system will scan an index page-by-page looking for the specified value(s) vs. using an optimized method such as a binary search. Index scan = page-by-page. Index seek = optimized method. — Mark Schneider
– Mark Schneider, Commented Aug 18, 2022 at 14:47

Hechi · Accepted Answer · 2022-10-26 04:42:47Z

16

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.

answered Oct 26, 2022 at 4:42

Hechi

6248 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related