From: hubert depesz lubaczewski <depesz@depesz.com>
To: Sanskar Agrawal <sanskar@flintk12.com>
Cc: pgsql-bugs mailing list <pgsql-bugs@postgresql.org>
Subject: Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
Date: Tue, 10 Sep 2024 09:52:56 +0200
Message-ID: <Zt_62MEdk0A0tKMY@depesz.com> (raw)
In-Reply-To: <CAL9bbZpt8DvT1kaepQc0rc2Disxth-jBPvD903r32v09LGTMvg@mail.gmail.com>
References: <18607-651aa996c1e71d48@postgresql.org>
<Zt_wyM5koKKmOmdn@depesz.com>
<CAL9bbZquC=BZa61TSHgX7vXjMN7BWtEgOCuzM8gwbn0jD3uvLg@mail.gmail.com>
<Zt_xfCSMgpVoxggF@depesz.com>
<CAL9bbZrrBYN8BxW51ihEYFNxDL0BN8WvgSq5x5UzWMs0OKUC4w@mail.gmail.com>
<Zt_4_jOQNLSLVFtf@depesz.com>
<CAL9bbZpt8DvT1kaepQc0rc2Disxth-jBPvD903r32v09LGTMvg@mail.gmail.com>
On Tue, Sep 10, 2024 at 01:20:22PM +0530, Sanskar Agrawal wrote:
> As you can see in the below screenshot if the view is defined with a UNION
> ALL it tends to drop the index + relations.
>
> [image: Screenshot 2024-09-10 at 1.17.27 PM.png]
>
> But if the same query is replaced with below, the constraints stay.
>
> [image: Screenshot 2024-09-10 at 1.18.59 PM.png]
>
> What I want to emphasize is after a "UNION ALL",
> if I want to further process on the unioned set -> it loses its indexes +
> relations which then makes the query planner opt in for a seq scan and
> table scans.
UNION ALL is irrelevant. There is no such thing as index on view. There
are indexes on materialized views, but not on plain, standard views.
View is just a rule to change query - there is nothing to index. When
you query view, pg will change your query to get data from underlying
table(s), and if there are suitable views *ON THE TABLE(S)* - they will
be used.
Best regards,
depesz
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: pgsql-bugs@postgresql.org
Cc: depesz@depesz.com, sanskar@flintk12.com
Subject: Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
In-Reply-To: <Zt_62MEdk0A0tKMY@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox