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

From: Tom Lane <[email-masked]>
Date: Tue, 13 May 2025 10:12:46 -0400

Markus Demleitner <[email-masked]> 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

---

From: Markus Demleitner <[email-masked]>
Date: Tue, 13 May 2025 16:48:11 +0200

Dear Tom,

> need to match.  Otherwise the UNIONs don't get flattened and you

Ahhhh... *source* tables.  Sure, once I'm out of desperation stun, it
kind of is unsurprising that the casts won't help me when it's about
pulling up the original tables.

Just to be sure: int and bigint don't mix, and neither do real and
double precision, right?  And the lazybone in me can't resist asking:
there's absolutely no way around this?

Thanks for setting my head straight,

         Markus