Thread: BUG #18607: UNION ALL discards all foreign key relations + indexes

The following bug has been logged on the website:

Bug reference:      18607
Logged by:          Sanskar Agrawal
Email address:      sanskar@flintk12.com
PostgreSQL version: 15.0
Operating system:   MacOS
Description:

Min Reproducable env :

Consider the query below -> 

"
CREATE OR REPLACE VIEW v3.temp_view AS
SELECT * FROM users WHERE id = 'e-1'
UNION ALL
SELECT * FROM users WHERE id = 'e-2';
"

groups.id is a primary key and has a unique index defined on the same.

Either way when the view is created, the indexes and the related relations
of the foreign key references are being dropped. 
(Same with "UNION").

What + Why? I am processing the result of the union in another CTE which now
is not having access to indices and so it is performing a seq scan!

PG Bug reporting form <noreply@postgresql.org> writes:
> CREATE OR REPLACE VIEW v3.temp_view AS
> SELECT * FROM users WHERE id = 'e-1'
> UNION ALL
> SELECT * FROM users WHERE id = 'e-2';

> Either way when the view is created, the indexes and the related relations
> of the foreign key references are being dropped. 

Views do not have either indexes or foreign keys.

> What + Why? I am processing the result of the union in another CTE which now
> is not having access to indices and so it is performing a seq scan!

This seems to be a performance complaint, rather than a valid bug
report.  You might find it helpful to read

https://wiki.postgresql.org/wiki/Slow_Query_Questions

            regards, tom lane

On Mon, Sep 09, 2024 at 03:16:33PM +0000, PG Bug reporting form wrote:
> Consider the query below -> 
> 
> "
> CREATE OR REPLACE VIEW v3.temp_view AS
> SELECT * FROM users WHERE id = 'e-1'
> UNION ALL
> SELECT * FROM users WHERE id = 'e-2';
> "
> 
> groups.id is a primary key and has a unique index defined on the same.

What is groups? Your view is named "temp_view", and it's using "users",
not "groups".

> Either way when the view is created, the indexes and the related relations
> of the foreign key references are being dropped. 
> (Same with "UNION").

What do you mean dropped? You had index on column id in table users, and
after view creation you don't have anymore?

Can you please show full example, that I can run on empty database, that
exhibits the problem?

Best regards,

depesz

On Tue, Sep 10, 2024 at 12:41:14PM +0530, Sanskar Agrawal wrote:
> Yes consider a small table as users with users.id as primary key,
> 
> took two rows from the same and ran a union all it tends to drop the
> indexes + foreign relations

Please show full example that I can run on empty DB.

Set of create table statement(s), adding indexes, foreign keys, and then
create view, that results in dropped indexes/foreign keys.

Best regards,

depesz

On Tue, Sep 10, 2024 at 12:52:21PM +0530, Sanskar Agrawal wrote:
> Please find the below DDLS:
> 
> CREATE TABLE test_users (
>     user_id SERIAL PRIMARY KEY,
>     username VARCHAR(50) NOT NULL,
>     email VARCHAR(100) NOT NULL,
>     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
> );
> -- Add an index on the user_id column
> CREATE INDEX idx_user_id ON test_users (user_id);
> -- Add an index on the email column
> CREATE INDEX idx_email ON test_users (email);
> INSERT INTO test_users (username, email) VALUES
> ('user1', 'user1@example.com'),
> ('user2', 'user2@example.com');
> SELECT * FROM temp_users WHERE email = 'user1@example.com'
> UNION ALL
> SELECT * FROM temp_users WHERE email = 'user2@example.com';

First, please note that your queries run with error:

#v+
=$ psql -aX -f z.sql
CREATE TABLE test_users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
-- Add an index on the user_id column
CREATE INDEX idx_user_id ON test_users (user_id);
CREATE INDEX
-- Add an index on the email column
CREATE INDEX idx_email ON test_users (email);
CREATE INDEX
INSERT INTO test_users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com');
INSERT 0 2
SELECT * FROM temp_users WHERE email = 'user1@example.com'
UNION ALL
SELECT * FROM temp_users WHERE email = 'user2@example.com';
psql:z.sql:16: ERROR:  relation "temp_users" does not exist
LINE 1: SELECT * FROM temp_users WHERE email = 'user1@example.com'
                      ^
#v-

You meant test_users, but had temp_users. Fixed that, re-ran:

#v+
=$ psql -aX -f z.sql
CREATE TABLE test_users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
-- Add an index on the user_id column
CREATE INDEX idx_user_id ON test_users (user_id);
CREATE INDEX
-- Add an index on the email column
CREATE INDEX idx_email ON test_users (email);
CREATE INDEX
INSERT INTO test_users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com');
INSERT 0 2
SELECT * FROM test_users WHERE email = 'user1@example.com'
UNION ALL
SELECT * FROM test_users WHERE email = 'user2@example.com';
 user_id | username |       email       |        created_at
---------+----------+-------------------+---------------------------
       1 | user1    | user1@example.com | 2024-09-10 09:43:05.72212
       2 | user2    | user2@example.com | 2024-09-10 09:43:05.72212
(2 rows)
#v-

So, the queries work.

Now, let's see the table:

#v+
=$ psql -aX <<< '\d test_users'
\d test_users
                                           Table "public.test_users"
   Column   |            Type             | Collation | Nullable |                   Default
------------+-----------------------------+-----------+----------+---------------------------------------------
 user_id    | integer                     |           | not null | nextval('test_users_user_id_seq'::regclass)
 username   | character varying(50)       |           | not null |
 email      | character varying(100)      |           | not null |
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "test_users_pkey" PRIMARY KEY, btree (user_id)
    "idx_email" btree (email)
    "idx_user_id" btree (user_id)
#v-

The index is there. So nothing got dropped.

Can you explain what you're seeing, preferably using tested queries that
show the problem?

Best regards,

depesz

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