Postgres does not use index with IN

<div>Hello.</div><div> </div><div>As example:</div><div><a href="https://www.db-fiddle.com/f/p9Zmec3Cqg8iTigzmj3Dr4/0">https://www.db-fiddle.com/f/p9Zmec3Cqg8iTigzmj3Dr4/0</a></div><div> </div><div><div><span style="font-family:'courier new' , monospace">DROP TABLE IF EXISTS parent CASCADE;</span></div><div><span style="font-family:'courier new' , monospace">DROP TABLE IF EXISTS child CASCADE;</span></div><div><span style="font-family:'courier new' , monospace">CREATE TABLE parent (id BIGSERIAL PRIMARY KEY);</span></div><div><span style="font-family:'courier new' , monospace">CREATE TABLE child (id BIGSERIAL PRIMARY KEY, parent_id BIGINT, dummy INT);</span></div><div><span style="font-family:'courier new' , monospace">CREATE INDEX c2p ON child USING btree (parent_id);</span></div><div><span style="font-family:'courier new' , monospace">INSERT INTO parent SELECT FROM generate_series(1, 5000);</span></div><div><span style="font-family:'courier new' , monospace">INSERT INTO child (parent_id) SELECT 1 + FLOOR(random()*5000) FROM generate_series(1, 100000);</span></div><div><span style="font-family:'courier new' , monospace">VACUUM ANALYZE parent;</span></div><div><span style="font-family:'courier new' , monospace">VACUUM ANALYZE child;</span></div><div> </div><div> </div><div><div><span style="font-family:'courier new' , monospace">-- Index c2p - USED</span></div><div><span style="font-family:'courier new' , monospace">-- <a href="https://explain.depesz.com/s/OgHk">https://explain.depesz.com/s/OgHk</a></span></div><div><span style="font-family:'courier new' , monospace">EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">WHERE ch.parent_id IN (1, 2, 3);</span></div><div> </div><div><span style="font-family:'courier new' , monospace">-- Index c2p - NOT USED (Seq Scan on child ch). Why?</span></div><div><span style="font-family:'courier new' , monospace">-- <a href="https://explain.depesz.com/s/kfX3">https://explain.depesz.com/s/kfX3</a></span></div><div><span style="font-family:'courier new' , monospace">EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">WHERE ch.parent_id IN (SELECT id FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */);</span></div><div> </div><div><span style="font-family:'courier new' , monospace">-- Index c2p - USED</span></div><div><span style="font-family:'courier new' , monospace">-- <a href="https://explain.depesz.com/s/6AQB">https://explain.depesz.com/s/6AQB</a></span></div><div><span style="font-family:'courier new' , monospace">EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">WHERE ch.parent_id = ANY ( (SELECT array_agg(id) FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */)::BIGINT[] );</span></div><div> </div><div> </div><div><div><div>Results:</div><div> </div><div><span style="font-family:'courier new' , monospace">**Query #1**</span></div><div> </div><div><span style="font-family:'courier new' , monospace">    EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">    SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">    WHERE ch.parent_id IN (1, 2, 3);</span></div><div> </div><div><span style="font-family:'courier new' , monospace">| QUERY PLAN                                                                                                           |</span></div><div><span style="font-family:'courier new' , monospace">| -------------------------------------------------------------------------------------------------------------------- |</span></div><div><span style="font-family:'courier new' , monospace">| Update on child ch  (cost=13.34..184.10 rows=59 width=26) (actual time=0.988..0.988 rows=0 loops=1)                  |</span></div><div><span style="font-family:'courier new' , monospace">|   -&gt;  Bitmap Heap Scan on child ch  (cost=13.34..184.10 rows=59 width=26) (actual time=0.043..0.314 rows=63 loops=1) |</span></div><div><span style="font-family:'courier new' , monospace">|         Recheck Cond: (parent_id = ANY ('{1,2,3}'::bigint[]))                                                        |</span></div><div><span style="font-family:'courier new' , monospace">|         Heap Blocks: exact=59                                                                                        |</span></div><div><span style="font-family:'courier new' , monospace">|         -&gt;  Bitmap Index Scan on c2p  (cost=0.00..13.33 rows=59 width=0) (actual time=0.025..0.025 rows=63 loops=1)  |</span></div><div><span style="font-family:'courier new' , monospace">|               Index Cond: (parent_id = ANY ('{1,2,3}'::bigint[]))                                                    |</span></div><div><span style="font-family:'courier new' , monospace">| Planning Time: 0.381 ms                                                                                              |</span></div><div><span style="font-family:'courier new' , monospace">| Execution Time: 1.030 ms                                                                                             |</span></div><div> </div><div> </div><div><span style="font-family:'courier new' , monospace">**Query #2**</span></div><div> </div><div><span style="font-family:'courier new' , monospace">    EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">    SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">    WHERE ch.parent_id IN (SELECT id FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */);</span></div><div> </div><div><span style="font-family:'courier new' , monospace">| QUERY PLAN                                                                                                                  |</span></div><div><span style="font-family:'courier new' , monospace">| --------------------------------------------------------------------------------------------------------------------------- |</span></div><div><span style="font-family:'courier new' , monospace">| Update on child ch  (cost=105.19..1908.91 rows=1500 width=32) (actual time=35.469..35.469 rows=0 loops=1)                   |</span></div><div><span style="font-family:'courier new' , monospace">|   -&gt;  Hash Join  (cost=105.19..1908.91 rows=1500 width=32) (actual time=35.102..35.121 rows=42 loops=1)                     |</span></div><div><span style="font-family:'courier new' , monospace">|         Hash Cond: (ch.parent_id = parent.id)                                                                               |</span></div><div><span style="font-family:'courier new' , monospace">|         -&gt;  Seq Scan on child ch  (cost=0.00..1541.00 rows=100000 width=22) (actual time=0.013..28.007 rows=100000 loops=1) |</span></div><div><span style="font-family:'courier new' , monospace">|         -&gt;  Hash  (cost=104.25..104.25 rows=75 width=14) (actual time=0.799..0.799 rows=2 loops=1)                          |</span></div><div><span style="font-family:'courier new' , monospace">|               Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                  |</span></div><div><span style="font-family:'courier new' , monospace">|               -&gt;  Seq Scan on parent  (cost=0.00..104.25 rows=75 width=14) (actual time=0.009..0.793 rows=2 loops=1)        |</span></div><div><span style="font-family:'courier new' , monospace">|                     Filter: ((id + 1) = ANY ('{1,2,3}'::bigint[]))                                                          |</span></div><div><span style="font-family:'courier new' , monospace">|                     Rows Removed by Filter: 4998                                                                            |</span></div><div><span style="font-family:'courier new' , monospace">| Planning Time: 0.400 ms                                                                                                     |</span></div><div><span style="font-family:'courier new' , monospace">| Execution Time: 35.501 ms                                                                                                   |</span></div><div> </div><div> </div><div><span style="font-family:'courier new' , monospace">**Query #3**</span></div><div> </div><div><span style="font-family:'courier new' , monospace">    EXPLAIN ANALYZE UPDATE child ch</span></div><div><span style="font-family:'courier new' , monospace">    SET dummy = 1</span></div><div><span style="font-family:'courier new' , monospace">    WHERE ch.parent_id = ANY ( (SELECT array_agg(id) FROM parent WHERE (id + 1) IN (1, 2, 3) /* Prevent to use PRIMARY KEY */)::BIGINT[] );</span></div><div> </div><div><span style="font-family:'courier new' , monospace">| QUERY PLAN                                                                                                            |</span></div><div><span style="font-family:'courier new' , monospace">| --------------------------------------------------------------------------------------------------------------------- |</span></div><div><span style="font-family:'courier new' , monospace">| Update on child ch  (cost=148.93..546.32 rows=200 width=26) (actual time=0.561..0.561 rows=0 loops=1)                 |</span></div><div><span style="font-family:'courier new' , monospace">|   InitPlan 1 (returns $0)                                                                                             |</span></div><div><span style="font-family:'courier new' , monospace">|     -&gt;  Aggregate  (cost=104.44..104.45 rows=1 width=32) (actual time=0.461..0.461 rows=1 loops=1)                    |</span></div><div><span style="font-family:'courier new' , monospace">|           -&gt;  Seq Scan on parent  (cost=0.00..104.25 rows=75 width=8) (actual time=0.004..0.458 rows=2 loops=1)       |</span></div><div><span style="font-family:'courier new' , monospace">|                 Filter: ((id + 1) = ANY ('{1,2,3}'::bigint[]))                                                        |</span></div><div><span style="font-family:'courier new' , monospace">|                 Rows Removed by Filter: 4998                                                                          |</span></div><div><span style="font-family:'courier new' , monospace">|   -&gt;  Bitmap Heap Scan on child ch  (cost=44.47..441.87 rows=200 width=26) (actual time=0.506..0.513 rows=42 loops=1) |</span></div><div><span style="font-family:'courier new' , monospace">|         Recheck Cond: (parent_id = ANY ($0))                                                                          |</span></div><div><span style="font-family:'courier new' , monospace">|         Heap Blocks: exact=2                                                                                          |</span></div><div><span style="font-family:'courier new' , monospace">|         -&gt;  Bitmap Index Scan on c2p  (cost=0.00..44.42 rows=200 width=0) (actual time=0.475..0.476 rows=94 loops=1)  |</span></div><div><span style="font-family:'courier new' , monospace">|               Index Cond: (parent_id = ANY ($0))                                                                      |</span></div><div><span style="font-family:'courier new' , monospace">| Planning Time: 0.107 ms                                                                                               |</span></div><div><span style="font-family:'courier new' , monospace">| Execution Time: 0.588 ms                                                                                              |</span></div><div> </div></div></div></div></div>