public inbox for [email protected]  

help / color / mirror / Atom feed

From: Markus Demleitner <[email protected]>
To: [email protected]
Subject: Re: Index not used in certain nested views but not in others
Date: Mon, 12 May 2025 16:12:17 +0200
Message-ID: <20250512141217.4dwnfxbaf53mhvi7@victor> (raw)
In-Reply-To: <[email protected]>
References: <20250430151647.7kootztymzznydn5@victor>
	<[email protected]>
	<[email protected]>

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

view thread (7+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Index not used in certain nested views but not in others
  In-Reply-To: <20250512141217.4dwnfxbaf53mhvi7@victor>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox