Date: Tue, 18 Jan 2022 10:24:34 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Arne Roland <A.Roland@index.de>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Message-ID: <202201181324.idvthmrhbtf5@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20220118065750.v5zigpuisljc6jmk@jrouhaud>
Archived-At: 
 <https://www.postgresql.org/message-id/202201181324.idvthmrhbtf5%40alvherre.pgsql>
Precedence: bulk

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/