Re: BUG #17964: Missed query planner optimization

On Wed, 7 Jun 2023 at 04:44, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> In the example below, the query planner uses a sequential scan (query 1)
> even though it could use an index scan (query 2).
>
> EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
> SELECT id, name FROM table2) AS q
> WHERE id IN (SELECT id FROM table3);

> EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
> SELECT id, name FROM table2) AS q
> WHERE id IN (1538,8836,5486,3464,2673);

It's not a bug that the planner does not consider evaluating the join
before the UNION, it's just an optimisation opportunity we don't
currently explore.

If you want that, then write:

EXPLAIN ANALYZE SELECT id, name FROM table1 WHERE id IN (SELECT id
FROM table3) UNION SELECT id, name FROM table2 WHERE id IN (SELECT id
FROM table3);

David