public inbox for [email protected]
help / color / mirror / Atom feedRe: Index not used in certain nested views but not in others
2+ messages / 2 participants
[nested] [flat]
* Re: Index not used in certain nested views but not in others
@ 2025-05-13 14:12 Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Lane @ 2025-05-13 14:12 UTC (permalink / raw)
To: Markus Demleitner <[email protected]>; +Cc: [email protected]
Markus Demleitner <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index not used in certain nested views but not in others
@ 2025-05-13 14:48 Markus Demleitner <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Markus Demleitner @ 2025-05-13 14:48 UTC (permalink / raw)
To: [email protected]
Dear Tom,
On Tue, May 13, 2025 at 10:12:46AM -0400, Tom Lane wrote:
> Markus Demleitner <[email protected]> writes:
> > SELECT
> > CAST(ssa_dstype AS text) AS dataproduct_type,
> > CAST(NULL AS text) AS dataproduct_subtype,
> > CAST(2 AS smallint) AS calib_level,
> > ...
>
> 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.
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-05-13 14:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-13 14:12 Re: Index not used in certain nested views but not in others Tom Lane <[email protected]>
2025-05-13 14:48 ` Markus Demleitner <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox