BUG #14107: Major query planner bug regarding subqueries and indices

The following bug has been logged on the website:

Bug reference:      14107
Logged by:          Mathias Kunter
Email address:      mathiaskunter@gmail.com
PostgreSQL version: 9.5.0
Operating system:   Windows 7
Description:

The query planner doesn't use an index although it could, causing an
unneccessary sequential table scan. Step by step instructions to reproduce
the problem are given below.


Step 1 - just create a simple test table with an indexed id column:

CREATE TABLE test (id serial NOT NULL, CONSTRAINT pkey PRIMARY KEY (id));


Step 2 - note that the index is used for the following query as expected:

EXPLAIN SELECT * FROM test WHERE id = 1 OR id IN (2);
                               QUERY PLAN
-------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=8.33..13.67 rows=2 width=4)
   Recheck Cond: ((id = 1) OR (id = 2))
   ->  BitmapOr  (cost=8.33..8.33 rows=2 width=0)
         ->  Bitmap Index Scan on pkey  (cost=0.00..4.16 rows=1 width=0)
               Index Cond: (id = 1)
         ->  Bitmap Index Scan on pkey  (cost=0.00..4.16 rows=1 width=0)
               Index Cond: (id = 2)


Step 3 - note that the index is NOT used for the following query:

EXPLAIN SELECT * FROM test WHERE id = 1 OR id IN (SELECT id FROM test WHERE
id = 2);
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Seq Scan on test  (cost=8.17..56.42 rows=1275 width=4)
   Filter: ((id = 1) OR (hashed SubPlan 1))
   SubPlan 1
     ->  Index Only Scan using pkey on test test_1  (cost=0.16..8.17 rows=1
width=4)
           Index Cond: (id = 2)