Date: Tue, 10 Sep 2024 09:52:56 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: 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
Message-ID: <Zt_62MEdk0A0tKMY@depesz.com>
Reply-To: depesz@depesz.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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAL9bbZpt8DvT1kaepQc0rc2Disxth-jBPvD903r32v09LGTMvg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Zt_62MEdk0A0tKMY%40depesz.com>
Precedence: bulk

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