Re: Index not used in certain nested views but not in others

On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote:
> Markus Demleitner <msdemlei(at)ari(dot)uni-heidelberg(dot)de> writes:
> > So, when the SELECT statement on dfbsspec.ssa stands along in the view
> > definition, Postgres does the right thing; when the exact same query
> > stands in a UNION ALL with other tables, Postgres doesn't use the
> > index. Hu?
>
> It's hard to be sure when you've shown us no table definitions and
> only fragments of the view definitions. But I suspect what is
> happening here is that the view's UNIONs are causing a data type
> coercion of raw_spectra.pub_did before it gets to the top level
> of the view output. That might interfere with the planner's ability
> to see that the outer query's join operator is compatible with
> the table's index.

For a more detailed description of that problem, see
https://www.cybertec-postgresql.com/en/union-all-data-types-performance/

Yours,
Laurenz Albe