Dear Tom, Dear Laurenz, Thanks for your pointers -- that was already helpful.
On Thu, May 01, 2025 at 06:58:45AM +0200, Laurenz Albe wrote: > On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote: > > 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 > > For a more detailed description of that problem, see > https://www.cybertec-postgresql.com/en/union-all-data-types-performance/ I've puzzled over this for a while, and while I'm sure the type mixing is what kills the index usage here, I've been unable to actually pinpoint where that happens. 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. Wouldn't that be good enough for the planner at least in the case of the "unreleated", non-constrained columns? In the meantime, I've dumped the minimal number of table definitions involved to https://docs.g-vo.org/tabledefs.txt -- I apologise for the mess, but at least it's stripped down to just two tables of the original 30-tables join. For type incongruencies in the *source* tables, you could look at accsize, which is integer vs. bigint (that would be enough to kill index use, right?), but as you can see postgres gets the cast in the k2c9vst leg of the obscore view (the no-op casts in the view creation aren't shown by postgres). Well, "can see"... ahem. I don't think I'm asking anyone to have a look at tabledefs.txt; but what I'd really be grateful for would be some trick that might guide me to the point where the planner actually decides it can't use the index, i.e., which column stops it). Is there such a thing, short of gdb-ing within pull_up_subqueries_recurse? Thanks, Markus
