On Wed, Oct 27, 2021 at 7:31 PM Tim Slechta <trslechta@xxxxxxxxx> wrote:
== Point 2. The equivalent query on the VL10N_OBJECT_NAME view executes a Seq Scan on the underlying pl10n_object_name. Why?
tc=# EXPLAIN ANALYZE select pval_0 from VL10N_OBJECT_NAME where pval_0 = 'xxxx';
Just to confirm and simplify, the question boils down to:
Why does:
SELECT * FROM view WHERE view.view_column = ?;
And view is:
CREATE VIEW AS
SELECT ..., view_column
FROM tbl1
UNION ALL
SELECT ..., view_column
FROM tbl2
;
Where tbl1 has an index on view_column AND tbl2 does not have an index on view_column
Result in a plan where both tb11 and tbl2 are sequentially scanned and the filter applied to the unioned result
Instead of a plan where the index lookup rows of tbl1 are supplied to the union and only tbl2 is sequentially scanned
?
I don't have an answer to offer up here. I'm pretty sure we do handle predicate pushdown into UNION ALL generally. I'm unclear exactly what the equivalently rewritten query would be in this case - but demonstrating that a query that doesn't use union all applies the index while the direct access of the view doesn't isn't sufficient to narrow down the problem. It can still either be the rule processing or the union processing that is seeming to make a wrong plan choice.
That isn't meant to discount the possibility that this case is actually correct - or at least the best we do presently for one or more technical reasons that I'm not familiar with...
David J.
