Message-ID: <91e617539ff887f1aac4e27bf1481b51fcf4b131.camel@cybertec.at>
Subject: Re: Index not used in certain nested views but not in others
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>, Markus Demleitner
	 <msdemlei@ari.uni-heidelberg.de>
Cc: pgsql-general@lists.postgresql.org
Date: Thu, 01 May 2025 06:58:45 +0200
In-Reply-To: <1400978.1746046929@sss.pgh.pa.us>
References: <20250430151647.7kootztymzznydn5@victor>
	 <1400978.1746046929@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/91e617539ff887f1aac4e27bf1481b51fcf4b131.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote:
> Markus Demleitner <msdemlei@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?
>=20
> 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