On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote: > Markus Demleitner <msdem...@ari.uni-heidelberg.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
