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.

Answer 1

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.

Answer 2

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.