From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Arne Roland <A.Roland@index.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Date: Tue, 18 Jan 2022 10:24:34 -0300
Message-ID: <202201181324.idvthmrhbtf5@alvherre.pgsql> (raw)
In-Reply-To: <20220118065750.v5zigpuisljc6jmk@jrouhaud>
On 2022-Jan-18, Julien Rouhaud wrote:
> 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)
Hmm, these plan changes look valid to me. A left self-join using the
primary key column? That looks optimizable all right.
I suspect that the author of partition-wise joins would want to change
these queries so that whatever was being tested by these self-joins is
tested by some other means (maybe just create an identical partitioned
table via CREATE TABLE fract_t2 ... ; INSERT INTO fract_t2 SELECT FROM
fract_t). But at the same time, the author of this patch should a) make
sure that the submitted patch updates these test results so that the
test pass, and also b) add some test cases to verify that his desired
behavior is tested somewhere, not just in a test case that's
incidentally broken by his patch.
What I still don't know is whether this patch is actually desirable or
not. If the only cases it affects is self-joins, is there much actual
value?
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
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: alvherre@alvh.no-ip.org, rjuju123@gmail.com, A.Roland@index.de
Subject: Re: missing indexes in indexlist with partitioned tables
In-Reply-To: <202201181324.idvthmrhbtf5@alvherre.pgsql>
* 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