From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Demleitner <msdemlei@ari.uni-heidelberg.de>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Index not used in certain nested views but not in others
In-reply-to: <20250512141217.4dwnfxbaf53mhvi7@victor>
References: <20250430151647.7kootztymzznydn5@victor> <1400978.1746046929@sss.pgh.pa.us> <91e617539ff887f1aac4e27bf1481b51fcf4b131.camel@cybertec.at> <20250512141217.4dwnfxbaf53mhvi7@victor>
Comments: In-reply-to Markus Demleitner <msdemlei@ari.uni-heidelberg.de>
	message dated "Mon, 12 May 2025 16:12:17 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3846.1747145566.1@sss.pgh.pa.us>
Date: Tue, 13 May 2025 10:12:46 -0400
Message-ID: <3847.1747145566@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3847.1747145566%40sss.pgh.pa.us>
Precedence: bulk

Markus Demleitner <msdemlei@ari.uni-heidelberg.de> writes:
> You see, when creating the "big", 30-tables view, I do cast all
> columns to common types in the view statement that actually make up
> the view.  The original SQL fragments look like this:

>   SELECT
>     CAST(ssa_dstype AS text) AS dataproduct_type,
>     CAST(NULL AS text) AS dataproduct_subtype,
>     CAST(2 AS smallint) AS calib_level,
>     ...

> and have a common source, so I'd believe by the time the things end
> up in the view, they should type-align even though their source
> tables do not.

Oh, well, that's your problem.  The source tables' column types
need to match.  Otherwise the UNIONs don't get flattened and you
don't get indexscans.

			regards, tom lane