public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Tom Lane <[email protected]>
To: Markus Demleitner <[email protected]>
Cc: [email protected]
Subject: Re: Index not used in certain nested views but not in others
Date: Thu, 01 May 2025 06:58:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <20250430151647.7kootztymzznydn5@victor>
<[email protected]>
On Wed, 2025-04-30 at 17:02 -0400, Tom Lane wrote:
> Markus Demleitner <[email protected]> writes:
> > So, when the SELECT statement on dfbsspec.ssa stands along in the view
> > definition, Postgres does the right thing; when the exact same query
> > stands in a UNION ALL with other tables, Postgres doesn't use the
> > index. Hu?
>
> 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
> to see that the outer query's join operator is compatible with
> the table's index.
For a more detailed description of that problem, see
https://www.cybertec-postgresql.com/en/union-all-data-types-performance/
Yours,
Laurenz Albe
view thread (7+ messages) latest in thread
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], [email protected], [email protected]
Subject: Re: Index not used in certain nested views but not in others
In-Reply-To: <[email protected]>
* 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