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

Mathias Kunter <mathiaskunter(at)gmail(dot)com> writes:
> Does anybody care? Shouldn't something like this be fixed ASAP??

As was already stated upthread, this is not a bug; it's an opportunity
for future improvement. It's unlikely to get "fixed ASAP" because there
would be quite a bit of work involved, possibly including new executor
infrastructure not just planner work.

As far as the first case goes, the executor doesn't currently have any
direct way to treat "a.x IN (SELECT ...)" as an indexqual for a.x.
In simple cases the planner can work around that by transforming the query
into a join against a unique-ified version of the sub-select, but that
doesn't work if the IN is underneath an OR. If you know that the
sub-select isn't going to return very many rows, you could do

SELECT ... FROM a WHERE a.x = ? OR a.y = ANY(ARRAY(SELECT ...));

but this would blow up rather badly with a large sub-select result,
so I'm not sure I want to try to make the planner transform it that
way automatically.

I don't actually see any way to do very much with your second example at
all:

> SELECT ... FROM a JOIN b ON (...) WHERE a.x = ? OR b.y = ?;

There's no way to push anything down to either the A or B scans from
that WHERE condition: you can't remove any rows before the join because
they might join to rows on the other side that satisfy the other half
of the OR.

regards, tom lane