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
