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

On 21 April 2016 at 23:56, <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 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)

Thanks for the report, but I think your subject line is a little
exaggerated. I'd describe this as a missed optimisation, and not even
a bug.

Having said that, I have looked previously at adding more smarts to
the planner around detecting when each relation can return, at most, a
single row, based on the restriction quals. When that can be proved,
instead of performing a join to that relation, use an init plan, such
as what you'd get if you'd written the query as;

EXPLAIN SELECT * FROM test WHERE id = 1 OR id = (SELECT id FROM test
WHERE id = 2);

then just remove the join/subplan altogether.

I'd not actually thought about expanding this to IN and NOT IN, but
likely it would be possible, providing NULLs could be handled
correctly too.

Was this the optimisation you think is missing? or did you expect the
subquery to feed the bitmap scan keys? If so I'm not too sure how that
could be made to work well when the row estimates are way off, and the
bitmap index scan has to deal with millions or billions of scan keys.
It might not end well.

As for if the IN() clause could be detected to return a single row...
well that's in my "I'd like to do that one day list".

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services