Date: Tue, 18 Jan 2022 14:57:50 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Arne Roland <A.Roland@index.de>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Message-ID: <20220118065750.v5zigpuisljc6jmk@jrouhaud>
References: <405b11a5a0504b76b997e312e543a69b@index.de>
 <202201171816.r6tkrn7drkm3@alvherre.pgsql>
 <2641568c18de40e8b1528fc9d4d80127@index.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2641568c18de40e8b1528fc9d4d80127@index.de>
Archived-At: 
 <https://www.postgresql.org/message-id/20220118065750.v5zigpuisljc6jmk%40jrouhaud>
Precedence: bulk

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)