Re: Views don't seem to use indexes?

On Wed, Oct 27, 2021 at 7:31 PM Tim Slechta <trslechta(at)gmail(dot)com> 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.