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 array_agg(r) as search_for_this
   from (select left(random()::text,9) as r
         from generate_series(1,2e4))_)
SELECT "products".* 
FROM "products", cte
WHERE "id" = ANY(search_for_this)
LIMIT 37520 OFFSET 0;

QUERY PLAN

Limit  (cost=62.59..100.77 rows=10 width=50) (actual time=22.415..191.538 rows=634 loops=1)

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

  CTE cte

    ->  Aggregate  (cost=22.50..22.52 rows=1 width=32) (actual time=10.096..10.097 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=2.768..8.188 rows=20000 loops=1)

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

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

  ->  Nested Loop  (cost=40.08..78.25 rows=10 width=50) (actual time=22.411..191.450 rows=634 loops=1)

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

        ->  CTE Scan on cte  (cost=0.00..0.02 rows=1 width=32) (actual time=10.203..10.204 rows=1 loops=1)

              Output: cte.search_for_this

        ->  Bitmap Heap Scan on public.products  (cost=40.08..78.13 rows=10 width=50) (actual time=12.192..181.093 rows=634 loops=1)

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

              Recheck Cond: (products.id = ANY (cte.search_for_this))

              Rows Removed by Index Recheck: 2

              Heap Blocks: exact=541

              ->  Bitmap Index Scan on product_id_idx2  (cost=0.00..40.08 rows=10 width=0) (actual time=11.749..11.750 rows=636 loops=1)

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

Planning Time: 0.332 ms

Execution Time: 191.778 ms

EXPLAIN