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

On Thu, Apr 21, 2016 at 4:56 AM, <mathiaskunter(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14107
> Logged by: Mathias Kunter
> Email address: mathiaskunter(at)gmail(dot)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​.