Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select setseed(.42);
CREATE TABLE public.products 
(
    id text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,-- NOT NULL,
    name character varying-- NOT NULL
);
insert into public.products (id)
select left(random()::text,9) from generate_series(1,3e5);

CREATE INDEX product_id_idx ON products(id);
CREATE INDEX product_id_idx2 ON products USING HASH(id);
CREATE INDEX product_id_pattern_idx ON products USING btree (id text_pattern_ops);

setseed

SELECT 1

CREATE TABLE

INSERT 0 300000

CREATE INDEX

CREATE INDEX

CREATE INDEX

vacuum analyze public.products;

VACUUM

explain analyze verbose
WITH cte AS MATERIALIZED
  (select unnest(arr) as search_for_this 
   from(select array_agg(r) arr
        from (select left(random()::text,9) as r
              from generate_series(1,2e4))_)_)
SELECT "products".* 
FROM "products"
WHERE "id" in (select search_for_this from cte)
LIMIT 37520 OFFSET 0;

QUERY PLAN

Limit  (cost=22.80..103.17 rows=10 width=50) (actual time=24.485..47.409 rows=634 loops=1)

  Output: products.id, products.created_at, products.name

  CTE cte

    ->  ProjectSet  (cost=22.50..22.57 rows=10 width=32) (actual time=12.157..14.241 rows=20000 loops=1)

          Output: unnest((array_agg(("left"((random())::text, 9)))))

          ->  Aggregate  (cost=22.50..22.52 rows=1 width=32) (actual time=12.137..12.138 rows=1 loops=1)

                Output: array_agg(("left"((random())::text, 9)))

                ->  Function Scan on pg_catalog.generate_series  (cost=0.00..20.00 rows=1000 width=32) (actual time=3.152..10.016 rows=20000 loops=1)

                      Output: "left"((random())::text, 9)

                      Function Call: generate_series('1'::numeric, '20000'::numeric)

  ->  Nested Loop  (cost=0.23..80.60 rows=10 width=50) (actual time=24.484..47.333 rows=634 loops=1)

        Output: products.id, products.created_at, products.name

        ->  HashAggregate  (cost=0.23..0.33 rows=10 width=32) (actual time=24.343..28.019 rows=19976 loops=1)

              Output: cte.search_for_this

              Group Key: cte.search_for_this

              Batches: 1  Memory Usage: 1833kB

              ->  CTE Scan on cte  (cost=0.00..0.20 rows=10 width=32) (actual time=12.161..18.573 rows=20000 loops=1)

                    Output: cte.search_for_this

        ->  Index Scan using product_id_idx2 on public.products  (cost=0.00..8.02 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=19976)

              Output: products.id, products.created_at, products.name

              Index Cond: (products.id = cte.search_for_this)

              Rows Removed by Index Recheck: 0

Planning Time: 0.934 ms

Execution Time: 47.807 ms

EXPLAIN