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

On Thu, Apr 21, 2016 at 4:56 AM, <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
> 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 reproduc=
e
> 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 =3D 1 OR id IN (2);
>                                QUERY PLAN
> -------------------------------------------------------------------------
>  Bitmap Heap Scan on test  (cost=3D8.33..13.67 rows=3D2 width=3D4)
>    Recheck Cond: ((id =3D 1) OR (id =3D 2))
>    ->  BitmapOr  (cost=3D8.33..8.33 rows=3D2 width=3D0)
>          ->  Bitmap Index Scan on pkey  (cost=3D0.00..4.16 rows=3D1 width=
=3D0)
>                Index Cond: (id =3D 1)
>          ->  Bitmap Index Scan on pkey  (cost=3D0.00..4.16 rows=3D1 width=
=3D0)
>                Index Cond: (id =3D 2)
>
>
> Step 3 - note that the index is NOT used for the following query:
>
> EXPLAIN SELECT * FROM test WHERE id =3D 1 OR id IN (SELECT id FROM test W=
HERE
> id =3D 2);
>                                      QUERY PLAN
>
> -------------------------------------------------------------------------=
------------
>  Seq Scan on test  (cost=3D8.17..56.42 rows=3D1275 width=3D4)
>    Filter: ((id =3D 1) OR (hashed SubPlan 1))
>    SubPlan 1
>      ->  Index Only Scan using pkey on test test_1  (cost=3D0.16..8.17 ro=
ws=3D1
> width=3D4)
>            Index Cond: (id =3D 2)
>
>
=E2=80=8BTo 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=E2=80=8B.