Optimizing fetching many rows with IN clause

Question

I'm trying to optimize a SELECT statement that occasionally fetches 10k-20k rows from 20M rows table.

To better explain my goals, let's say that I have table big_table configured as following:

public=> \d+ big_table
                                        Table "public.big_table"
     Column      |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 date_stored     | timestamp with time zone |           | not null |         | plain    |              |
 last_modified   | timestamp with time zone |           | not null |         | plain    |              |
 id              | uuid                     |           | not null |         | plain    |              |
 hash_key        | text                     |           | not null |         | extended |              |
 data            | text                     |           | not null |         | extended |              |
Indexes:
    "big_table_pkey" PRIMARY KEY, btree (id)
    "big_table_hash_key_unique" UNIQUE CONSTRAINT, btree (hash_key)

Now, my code run this simple statement

SELECT
    big_table.hash_key
FROM
    big_table
WHERE
    big_table.hash_key IN (
        'cebb5ffd54549727a1aa018c941d54c50fb2f897',
        '80b7c5513b7dac756492627bd871521507f7d6a9',
        'd6d74f5495a2f6bf089fc640c60425ba4be59838',
        ...
        ...
        -- 10,000 keys here
    );

While this may varies, it usually takes between 5s - 10s to run sampled from my database logs.

I'm trying to improve this without changing the logic and I came across this post that might not be relevant for new postgres version (I'm using PG13.7). One suggested solution is to rewrite the statement to use VALUES inside in IN clause.

When running benchmarks (multiple times to set the cache), I can hardly see any difference between using VALUES (constant table) and not.

Here is the original plan (when heavily using the cache):

--------------------------------------------------------------------------------------------------------------------
 Index Only Scan using big_table_hash_key_unique on big_table  (cost=0.56..105125.50 rows=19444 width=41) (actual time=28.831..175.550 rows=19444 loops=1)
   Index Cond: (hash_key = ANY ('{HASH_1, HASH_2, HASH_3, .... -- 20,000 keys}'::text[]))
   Heap Fetches: 19398
   Buffers: shared hit=99628
 Planning:
   Buffers: shared hit=164
 Planning Time: 12.789 ms
 Execution Time: 176.781 ms
(8 rows)

Here is the plan of the improved statement (when using VALUES):

--------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=292.22..105519.76 rows=19444 width=41) (actual time=10.051..202.510 rows=19444 loops=1)
   Buffers: shared hit=99743
   ->  HashAggregate  (cost=291.66..486.10 rows=19444 width=32) (actual time=10.000..15.310 rows=19444 loops=1)
         Group Key: "*VALUES*".column1
         Batches: 1  Memory Usage: 2833kB
         ->  Values Scan on "*VALUES*"  (cost=0.00..243.05 rows=19444 width=32) (actual time=0.001..4.185 rows=19444 loops=1)
   ->  Index Only Scan using big_table_hash_key_unique on big_table  (cost=0.56..5.41 rows=1 width=41) (actual time=0.009..0.009 rows=1 loops=19444)
         Index Cond: (hash_key = "*VALUES*".column1)
         Heap Fetches: 19398
         Buffers: shared hit=99743
 Planning:
   Buffers: shared hit=136
 Planning Time: 12.346 ms
 Execution Time: 204.292 ms
(14 rows)

Does the suggested VALUES solution is not relevant anymore in PG13+?

Answer 1

The =ANY when used to drive an index scan is implemented quite differently than when used as a filter. The advice to rewrite to a VALUES list still applies in 13.7, just not in your particular (indexed) usage.

Note that the filter usage did get improved, but not until v14.