Postgres is doing a sequential scan when an index scan in simplest possible query

Question

Variants of this question have been asked multiple times before but in my case the query is the simplest query one could possibly do: select * from table_name where id = ?. Here's all the steps I took:

create extension "uuid-ossp";
create table test (id uuid primary key default uuid_generate_v4());
insert into test select from generate_series(1, 1000000);
explain analyze select * from test where id = uuid_generate_v4();

Here's the output:

CREATE EXTENSION
CREATE TABLE
INSERT 0 1000000
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12656.79 rows=1 width=16) (actual time=593.754..596.870 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..11656.69 rows=1 width=16) (actual time=572.223..572.223 rows=0 loops=3)
         Filter: (id = uuid_generate_v4())
         Rows Removed by Filter: 333333
 Planning Time: 0.113 ms
 Execution Time: 596.887 ms
(8 rows)

This makes absolutely no sense to me. Why wouldn't Postgres opt to do an index scan here?

I'm using the latest version of Postgres, v13.3.

jjanes · Accepted Answer · 2021-06-03 16:53:48Z

8

Your own answer is correct, but doesn't really explain why. The explanation is that uuid_generate_v4() is a volatile function. The way PostgreSQL implements that, it is executing it one time for each row in the table, and compares the result--a different one each time--to the stored value for that row.

You can trick PostgreSQL by putting it into a subquery, as PostgreSQL doesn't track the volatility of subqueries.

explain analyze select * from test where id = (select uuid_generate_v4());

Now uuid_generate_v4() is called only once, and it will use the index.

answered Jun 3, 2021 at 16:53

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Comments

Miguel Oller · Accepted Answer · 2021-06-03 16:20:14Z

3

My mistake was to use uuid_generate_v4() in the where clause instead of a single UUID. Using a UUID value like '51e7c7e5-63a5-46ea-a698-9066b869d208' does result in an index scan.

answered Jun 3, 2021 at 16:20

Miguel Oller

12710 bronze badges

Collectives™ on Stack Overflow

Postgres is doing a sequential scan when an index scan in simplest possible query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related