IronPort-Data: A9a23:Q7xEMaoHtHt6057qyWciRSWBhqFeBmIQZRIvgKrLsJaIsI4StFCzt
 garIBnXOvjbMGugKYt+O9mx9BhVsJXXzNdrGVQ4/3tnQihHpePIVI+TRqvSF3rPcZeTEh4PA
 +byyDXkBJppJpMJjk71atANlVEliOfQAOC6ULWYUsxIbVcMYD87jh5+kPIOjIdtgNyoayuAo
 tqaT/f3YDdJ4BYqdDtJg06/gEk35qmq5WpC5gVWic1j5TcyqVFEVPrzGonsdxMUcqEMdsamS
 uDKyq2O/2+x13/B3fv8z94X2mVTKlLjFVDmZkh+AsBOsTAazsAG6ZvXAdJHAathZ5VlqPgqo
 DlFncTYpQ7EpcQgksxFO/VTO3kW0aGrZNYrLFDn2fF/wXEqfFP+499fV3wPBrcmufdbDER8/
 vsqOisSO0Xra+KemNpXS8FlgsIiKMDuZsUWoHAm0DfYSPYrQJzOR6/H/9Ae0DpYasJmRKyPI
 ZBBN3w2NU6GPkIn1lQ/UfrSmM+tnXXydjlVqXqcv6tx/m7SiQx80bTgNNDYYNPMScg9ckOw/
 D6ZrzimWk1EXDCZ4TSp4FGqhOCepw7ESqctKoW+tdFDw2TGkwT/DzVMDADq/KLn4qKkYPpHJ
 FcN/m8hoK4/7kWvZt38RAGj5maDpQITQddLCul87xuCopc4+C6QAm4FSjdELs4vtIkrQz1v1
 lKIk97tDzF1v/uZRBpx64t4sxugGQIkdlI7XhU6Dhop7vzRq4YBnAnQG4ML/LGOsvX5HjT5w
 javpSc4hqkOgcNj60ld1QyW695LjsWSJjPZ9jnqsnSZAhRRSKPNWmBFwVHf6/dEJYPfXFCB+
 mUCmo2e4eECAJWHmTaCBukAdF1I2xpnGGGE6bKMN8B/n9hIx5JEVdoIiAyS3G8zbq45lcbBO
 Sc+eWp5vfe/xkeCY65teJ6WAM8316XmHtmNfqmLMoQfMsgqKVLbonoGiausM4bFzBlEfUYXZ
 MzzTCpQJSty5VlPlWHvHL1NuVPV7nlnnT+7qW/HI+SPi+PFNSPOF9/pwXOTaOEn4bmfoRnc/
 soXN8aLxxhbWuDkbUHqHX07czg3wLlSLc6elvG7gcbYeFY3SD98VqSOqV7jEqQ895loei7z1
 inVcidlJJDX3BUr9S3ihqhfVY7S
IronPort-HdrOrdr: A9a23:TYsegq7VB53oXH+eEQPXwP/XdLJyesId70hD6qkoc20wTiXqrb
 HIoB17726OtN9/YhEdcLy7VZVoBEmskKKdgrNhR4tKPjOW21dARbsKheCJ/9SjIVydygc378
 hdmt1FaeEYemIVsS+V2mWF+p0bsb+6GeiT9J7jJllWPHlXV50=
Date: Mon, 12 May 2025 16:12:17 +0200
From: Markus Demleitner <msdemlei@ari.uni-heidelberg.de>
To: pgsql-general@lists.postgresql.org
Subject: Re: Index not used in certain nested views but not in others
Message-ID: <20250512141217.4dwnfxbaf53mhvi7@victor>
References: <20250430151647.7kootztymzznydn5@victor>
 <1400978.1746046929@sss.pgh.pa.us>
 <91e617539ff887f1aac4e27bf1481b51fcf4b131.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <91e617539ff887f1aac4e27bf1481b51fcf4b131.camel@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/20250512141217.4dwnfxbaf53mhvi7%40victor>
Precedence: bulk

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