pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18607: UNION ALL discards all foreign key relations + indexes
6+ messages / 3 participants
[nested] [flat]
* BUG #18607: UNION ALL discards all foreign key relations + indexes
@ 2024-09-09 15:16 PG Bug reporting form <noreply@postgresql.org>
2024-09-09 16:29 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes Tom Lane <tgl@sss.pgh.pa.us>
2024-09-10 07:10 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2024-09-09 15:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: sanskar@flintk12.com
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
@ 2024-09-09 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2024-09-09 16:29 UTC (permalink / raw)
To: sanskar@flintk12.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
@ 2024-09-10 07:10 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:13 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2024-09-10 07:10 UTC (permalink / raw)
To: sanskar@flintk12.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
2024-09-10 07:10 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
@ 2024-09-10 07:13 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:45 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2024-09-10 07:13 UTC (permalink / raw)
To: Sanskar Agrawal <sanskar@flintk12.com>; +Cc: pgsql-bugs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
2024-09-10 07:10 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:13 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
@ 2024-09-10 07:45 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:52 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2024-09-10 07:45 UTC (permalink / raw)
To: Sanskar Agrawal <sanskar@flintk12.com>; +Cc: pgsql-bugs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
2024-09-10 07:10 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:13 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:45 ` Re: BUG #18607: UNION ALL discards all foreign key relations + indexes hubert depesz lubaczewski <depesz@depesz.com>
@ 2024-09-10 07:52 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2024-09-10 07:52 UTC (permalink / raw)
To: Sanskar Agrawal <sanskar@flintk12.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-09-10 07:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-09 15:16 BUG #18607: UNION ALL discards all foreign key relations + indexes PG Bug reporting form <noreply@postgresql.org>
2024-09-09 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-10 07:10 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:13 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:45 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-09-10 07:52 ` hubert depesz lubaczewski <depesz@depesz.com>
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