- Notifications
You must be signed in to change notification settings - Fork69
Fixes related to new changes in PostgreSQL 16: October 25, 2022 - November 15, 2022#259
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
Merged
Uh oh!
There was an error while loading.Please reload this page.
Merged
Changes fromall commits
Commits
File filter
Filter by extension
Conversations
Failed to load comments.
Loading
Uh oh!
There was an error while loading.Please reload this page.
Jump to
Jump to file
Failed to load files.
Loading
Uh oh!
There was an error while loading.Please reload this page.
Diff view
Diff view
There are no files selected for viewing
161 changes: 161 additions & 0 deletionsexpected/pathman_join_clause_4.out
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.Learn more about bidirectional Unicode characters
| Original file line number | Diff line number | Diff line change |
|---|---|---|
| @@ -0,0 +1,161 @@ | ||
| /* | ||
| * Since 8edd0e794 (>= 12) Append nodes with single subplan are eliminated, | ||
| * causing different output; pathman_gaps_1.out is the updated version. | ||
| */ | ||
| \set VERBOSITY terse | ||
| SET search_path = 'public'; | ||
| CREATE SCHEMA pathman; | ||
| CREATE EXTENSION pg_pathman SCHEMA pathman; | ||
| CREATE SCHEMA test; | ||
| /* | ||
| * Test push down a join clause into child nodes of append | ||
| */ | ||
| /* create test tables */ | ||
| CREATE TABLE test.fk ( | ||
| id1 INT NOT NULL, | ||
| id2 INT NOT NULL, | ||
| start_key INT, | ||
| end_key INT, | ||
| PRIMARY KEY (id1, id2)); | ||
| CREATE TABLE test.mytbl ( | ||
| id1 INT NOT NULL, | ||
| id2 INT NOT NULL, | ||
| key INT NOT NULL, | ||
| CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2), | ||
| PRIMARY KEY (id1, key)); | ||
| SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8); | ||
| create_hash_partitions | ||
| ------------------------ | ||
| 8 | ||
| (1 row) | ||
| /* ...fill out with test data */ | ||
| INSERT INTO test.fk VALUES (1, 1); | ||
| INSERT INTO test.mytbl VALUES (1, 1, 5), (1, 1, 6); | ||
| /* gather statistics on test tables to have deterministic plans */ | ||
| ANALYZE; | ||
| /* run test queries */ | ||
| EXPLAIN (COSTS OFF) /* test plan */ | ||
| SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key | ||
| FROM test.mytbl m JOIN test.fk USING(id1, id2) | ||
| WHERE NOT key <@ int4range(6, end_key); | ||
| QUERY PLAN | ||
| ------------------------------------------------------------------------------------------------------- | ||
| Nested Loop | ||
| -> Seq Scan on fk | ||
| -> Custom Scan (RuntimeAppend) | ||
| Prune by: (m.id1 = fk.id1) | ||
| -> Seq Scan on mytbl_0 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_1 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_2 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_3 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_4 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_5 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_6 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| -> Seq Scan on mytbl_7 m | ||
| Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key)))) | ||
| (20 rows) | ||
| /* test joint data */ | ||
| SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key | ||
| FROM test.mytbl m JOIN test.fk USING(id1, id2) | ||
| WHERE NOT key <@ int4range(6, end_key); | ||
| tableoid | id1 | id2 | key | start_key | end_key | ||
| --------------+-----+-----+-----+-----------+--------- | ||
| test.mytbl_6 | 1 | 1 | 5 | | | ||
| (1 row) | ||
| /* | ||
| * Test case by @dimarick | ||
| */ | ||
| CREATE TABLE test.parent ( | ||
| id SERIAL NOT NULL, | ||
| owner_id INTEGER NOT NULL | ||
| ); | ||
| CREATE TABLE test.child ( | ||
| parent_id INTEGER NOT NULL, | ||
| owner_id INTEGER NOT NULL | ||
| ); | ||
| CREATE TABLE test.child_nopart ( | ||
| parent_id INTEGER NOT NULL, | ||
| owner_id INTEGER NOT NULL | ||
| ); | ||
| INSERT INTO test.parent (owner_id) VALUES (1), (2), (3), (3); | ||
| INSERT INTO test.child (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3); | ||
| INSERT INTO test.child_nopart (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3); | ||
| SELECT pathman.create_hash_partitions('test.child', 'owner_id', 2); | ||
| create_hash_partitions | ||
| ------------------------ | ||
| 2 | ||
| (1 row) | ||
| /* gather statistics on test tables to have deterministic plans */ | ||
| ANALYZE; | ||
| /* Query #1 */ | ||
| EXPLAIN (COSTS OFF) SELECT * FROM test.parent | ||
| LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND | ||
| test.child.owner_id = test.parent.owner_id | ||
| WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); | ||
| QUERY PLAN | ||
| ----------------------------------------------------------------------------------------------------- | ||
| Nested Loop Left Join | ||
| -> Seq Scan on parent | ||
| Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3)) | ||
| -> Custom Scan (RuntimeAppend) | ||
| Prune by: ((child.owner_id = 3) AND (child.owner_id = parent.owner_id)) | ||
| -> Seq Scan on child_1 child | ||
| Filter: ((owner_id = 3) AND (owner_id = parent.owner_id) AND (parent_id = parent.id)) | ||
| (7 rows) | ||
| SELECT * FROM test.parent | ||
| LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND | ||
| test.child.owner_id = test.parent.owner_id | ||
| WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); | ||
| id | owner_id | parent_id | owner_id | ||
| ----+----------+-----------+---------- | ||
| 3 | 3 | 3 | 3 | ||
| 4 | 3 | | | ||
| (2 rows) | ||
| /* Query #2 */ | ||
| EXPLAIN (COSTS OFF) SELECT * FROM test.parent | ||
| LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND | ||
| test.child.owner_id = 3 | ||
| WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); | ||
| QUERY PLAN | ||
| ---------------------------------------------------------------------- | ||
| Nested Loop Left Join | ||
| Join Filter: (child.parent_id = parent.id) | ||
| -> Seq Scan on parent | ||
| Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3)) | ||
| -> Seq Scan on child_1 child | ||
| Filter: (owner_id = 3) | ||
| (6 rows) | ||
| SELECT * FROM test.parent | ||
| LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND | ||
| test.child.owner_id = 3 | ||
| WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4); | ||
| id | owner_id | parent_id | owner_id | ||
| ----+----------+-----------+---------- | ||
| 3 | 3 | 3 | 3 | ||
| 4 | 3 | | | ||
| (2 rows) | ||
| DROP TABLE test.child CASCADE; | ||
| NOTICE: drop cascades to 2 other objects | ||
| DROP TABLE test.child_nopart CASCADE; | ||
| DROP TABLE test.mytbl CASCADE; | ||
| NOTICE: drop cascades to 8 other objects | ||
| DROP TABLE test.fk CASCADE; | ||
| DROP TABLE test.parent CASCADE; | ||
| DROP SCHEMA test; | ||
| DROP EXTENSION pg_pathman CASCADE; | ||
| DROP SCHEMA pathman; |
128 changes: 128 additions & 0 deletionsexpected/pathman_lateral_4.out
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.Learn more about bidirectional Unicode characters
| Original file line number | Diff line number | Diff line change |
|---|---|---|
| @@ -0,0 +1,128 @@ | ||
| /* | ||
| * Sometimes join selectivity improvements patches in pgpro force nested loop | ||
| * members swap -- in pathman_lateral_1.out and pathman_lateral_3.out | ||
| * | ||
| * Since 55a1954da16 and 6ef77cf46e8 (>= 13) output of EXPLAIN was changed, | ||
| * now it includes aliases for inherited tables. | ||
| */ | ||
| \set VERBOSITY terse | ||
| SET search_path = 'public'; | ||
| CREATE EXTENSION pg_pathman; | ||
| CREATE SCHEMA test_lateral; | ||
| /* create table partitioned by HASH */ | ||
| create table test_lateral.data(id int8 not null); | ||
| select create_hash_partitions('test_lateral.data', 'id', 10); | ||
| create_hash_partitions | ||
| ------------------------ | ||
| 10 | ||
| (1 row) | ||
| insert into test_lateral.data select generate_series(1, 10000); | ||
| VACUUM ANALYZE; | ||
| set enable_hashjoin = off; | ||
| set enable_mergejoin = off; | ||
| /* all credits go to Ivan Frolkov */ | ||
| explain (costs off) | ||
| select * from | ||
| test_lateral.data as t1, | ||
| lateral(select * from test_lateral.data as t2 where t2.id > t1.id) t2, | ||
| lateral(select * from test_lateral.data as t3 where t3.id = t2.id + t1.id) t3 | ||
| where t1.id between 1 and 100 and | ||
| t2.id between 2 and 299 and | ||
| t1.id > t2.id and | ||
| exists(select * from test_lateral.data t | ||
| where t1.id = t2.id and t.id = t3.id); | ||
| QUERY PLAN | ||
| -------------------------------------------------------------------------------------------- | ||
| Nested Loop | ||
| -> Nested Loop | ||
| Join Filter: ((t2.id + t1.id) = t.id) | ||
| -> HashAggregate | ||
| Group Key: t.id | ||
| -> Append | ||
| -> Seq Scan on data_0 t_1 | ||
| -> Seq Scan on data_1 t_2 | ||
| -> Seq Scan on data_2 t_3 | ||
| -> Seq Scan on data_3 t_4 | ||
| -> Seq Scan on data_4 t_5 | ||
| -> Seq Scan on data_5 t_6 | ||
| -> Seq Scan on data_6 t_7 | ||
| -> Seq Scan on data_7 t_8 | ||
| -> Seq Scan on data_8 t_9 | ||
| -> Seq Scan on data_9 t_10 | ||
| -> Materialize | ||
| -> Nested Loop | ||
| Join Filter: ((t2.id > t1.id) AND (t1.id > t2.id) AND (t1.id = t2.id)) | ||
| -> Append | ||
| -> Seq Scan on data_0 t2_1 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_1 t2_2 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_2 t2_3 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_3 t2_4 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_4 t2_5 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_5 t2_6 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_6 t2_7 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_7 t2_8 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_8 t2_9 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Seq Scan on data_9 t2_10 | ||
| Filter: ((id >= 2) AND (id <= 299)) | ||
| -> Materialize | ||
| -> Append | ||
| -> Seq Scan on data_0 t1_1 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_1 t1_2 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_2 t1_3 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_3 t1_4 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_4 t1_5 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_5 t1_6 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_6 t1_7 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_7 t1_8 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_8 t1_9 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Seq Scan on data_9 t1_10 | ||
| Filter: ((id >= 1) AND (id <= 100)) | ||
| -> Custom Scan (RuntimeAppend) | ||
| Prune by: (t3.id = t.id) | ||
| -> Seq Scan on data_0 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_1 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_2 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_3 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_4 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_5 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_6 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_7 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_8 t3 | ||
| Filter: (t.id = id) | ||
| -> Seq Scan on data_9 t3 | ||
| Filter: (t.id = id) | ||
| (84 rows) | ||
| set enable_hashjoin = on; | ||
| set enable_mergejoin = on; | ||
| DROP TABLE test_lateral.data CASCADE; | ||
| NOTICE: drop cascades to 10 other objects | ||
| DROP SCHEMA test_lateral; | ||
| DROP EXTENSION pg_pathman; |
Oops, something went wrong.
Uh oh!
There was an error while loading.Please reload this page.
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.