Re: missing indexes in indexlist with partitioned tables - Mailing list pgsql-hackers

From Julien Rouhaud
Subject Re: missing indexes in indexlist with partitioned tables
Date
Msg-id 20220118065750.v5zigpuisljc6jmk@jrouhaud
Whole thread Raw
In response to Re: missing indexes in indexlist with partitioned tables  (Arne Roland <A.Roland@index.de>)
Responses Re: missing indexes in indexlist with partitioned tables
List pgsql-hackers
Hi,

On Mon, Jan 17, 2022 at 08:32:40PM +0000, Arne Roland wrote:
> 
> Afaiac the join pruning where the outer table is a partitioned table is the relevant case.

The last version of the patch now fails on all platform, with plan changes.

For instance:
https://cirrus-ci.com/task/4825629131538432
https://api.cirrus-ci.com/v1/artifact/task/4825629131538432/regress_diffs/src/test/regress/regression.diffs
diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/partition_join.out
/tmp/cirrus-ci-build/src/test/regress/results/partition_join.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/partition_join.out    2022-01-17 23:08:47.158198249 +0000
+++ /tmp/cirrus-ci-build/src/test/regress/results/partition_join.out    2022-01-17 23:12:34.163488567 +0000
@@ -4887,37 +4887,23 @@
 SET enable_partitionwise_join = on;
 EXPLAIN (COSTS OFF)
 SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id ASC LIMIT 10;
-                              QUERY PLAN
------------------------------------------------------------------------
+                           QUERY PLAN
+-----------------------------------------------------------------
  Limit
-   ->  Merge Append
-         Sort Key: x.id
-         ->  Merge Left Join
-               Merge Cond: (x_1.id = y_1.id)
-               ->  Index Only Scan using fract_t0_pkey on fract_t0 x_1
-               ->  Index Only Scan using fract_t0_pkey on fract_t0 y_1
-         ->  Merge Left Join
-               Merge Cond: (x_2.id = y_2.id)
-               ->  Index Only Scan using fract_t1_pkey on fract_t1 x_2
-               ->  Index Only Scan using fract_t1_pkey on fract_t1 y_2
-(11 rows)
+   ->  Append
+         ->  Index Only Scan using fract_t0_pkey on fract_t0 x_1
+         ->  Index Only Scan using fract_t1_pkey on fract_t1 x_2
+(4 rows)

 EXPLAIN (COSTS OFF)
 SELECT * FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY id DESC LIMIT 10;
-                                   QUERY PLAN
---------------------------------------------------------------------------------
+                                QUERY PLAN
+--------------------------------------------------------------------------
  Limit
-   ->  Merge Append
-         Sort Key: x.id DESC
-         ->  Nested Loop Left Join
-               ->  Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
-               ->  Index Only Scan using fract_t0_pkey on fract_t0 y_1
-                     Index Cond: (id = x_1.id)
-         ->  Nested Loop Left Join
-               ->  Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
-               ->  Index Only Scan using fract_t1_pkey on fract_t1 y_2
-                     Index Cond: (id = x_2.id)
-(11 rows)
+   ->  Append
+         ->  Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
+         ->  Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
+(4 rows)



pgsql-hackers by date:

Previous
From: Shruthi Gowda
Date:
Subject: Re: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce)
Next
From: Julien Rouhaud
Date:
Subject: Re: pg_ls_tmpdir to show directories and shared filesets (and pg_ls_*)