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

The problem unfortunately persists in 9.5.2 as well, where the query
plan is exactly the same as in 9.5.0. (Tested on Windows 7, but this
presumably is a cross-platform bug.) From what I can tell, this affects
all queries of the type

SELECT ... WHERE <condition> OR <indexed_column> IN (<subselect>);

As a workaround, you can use UNION, which works as expected:

SELECT ... WHERE <condition> UNION SELECT ... WHERE <indexed_column> IN
(<subselect>);

However, as it currently stands, queries of the above form are de facto
unusable with PostgreSQL. So this is pretty serious IMO.


Am 21.04.2016 um 23:01 schrieb David G. Johnston:
> On Thu, Apr 21, 2016 at 4:56 AM, <mathiaskunter@gmail.com
> <mailto:mathiaskunter@gmail.com>>wrote:
>
>     The following bug has been logged on the website:
>
>     Bug reference:      14107
>     Logged by:          Mathias Kunter
>     Email address:      mathiaskunter@gmail.com
>     <mailto: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)
>
>
> ​To lazy to research at the moment but I think this has been fixed and
> released.  You show 9.5.0 as your version.  Update and you should be fine.
>
> David J​.
>