Re: BUG #18607: UNION ALL discards all foreign key relations + indexes - Mailing list pgsql-bugs

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