On Wed, 7 Jun 2023 at 04:44, PG Bug reporting form
<noreply@postgresql.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