From: Julien Rouhaud <rjuju123@gmail.com>
To: Arne Roland <A.Roland@index.de>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Date: Tue, 18 Jan 2022 14:57:50 +0800
Message-ID: <20220118065750.v5zigpuisljc6jmk@jrouhaud> (raw)
In-Reply-To: <2641568c18de40e8b1528fc9d4d80127@index.de>
References: <405b11a5a0504b76b997e312e543a69b@index.de>
<202201171816.r6tkrn7drkm3@alvherre.pgsql>
<2641568c18de40e8b1528fc9d4d80127@index.de>
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/4825629131538432https://api.cirrus-ci.com/v1/artifact/task/4825629131538432/regress_diffs/src/test/regress/regressio...
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: rjuju123@gmail.com, A.Roland@index.de, alvherre@alvh.no-ip.org
Subject: Re: missing indexes in indexlist with partitioned tables
In-Reply-To: <20220118065750.v5zigpuisljc6jmk@jrouhaud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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