On Fri, Oct 11, 2019 at 10:48:37AM -0400, Tom Lane wrote:
> Justin Pryzby <[email protected]> writes:
> > The view is actually a join of two relkind=p partitioned tables (which I
> > will acknowledge probably performs poorly).
>
> Could you provide a self-contained test case please?
Working on it. FWIW explain for a v11 customer looks like this:
Nested Loop (cost=10000011818.10..10000076508.23 rows=734500 width=159)
Join Filter: ((s.site_location = ''::text) OR (s.site_office =
(COALESCE(huawei_ggsn_201610.ne_name, huawei_ggsn_gw_201610.ne_name))))
-> Group (cost=11818.10..11946.31 rows=2937 width=40)
Group Key: (COALESCE(huawei_ggsn_201610.start_time,
huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name,
huawei_ggsn_gw_201610.ne_name))
-> Merge Append (cost=11818.10..11931.59 rows=2944 width=40)
Sort Key: (COALESCE(huawei_ggsn_201610.start_time,
huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name,
huawei_ggsn_gw_201610.ne_name))
-> Group (cost=332.48..333.10 rows=83 width=40)
Group Key: (COALESCE(huawei_ggsn_201610.start_time,
huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name,
huawei_ggsn_gw_201610.ne_name))
-> Sort (cost=332.48..332.69 rows=83 width=40)
Sort Key: (COALESCE(huawei_ggsn_201610.start_time,
huawei_ggsn_gw_201610.start_time)), (COALESCE(huawei_ggsn_201610.ne_name,
huawei_ggsn_gw_201610.ne_name))
-> Hash Full Join (cost=46.48..329.84 rows=83
width=40)
Hash Cond: ((huawei_ggsn_201610.ne_name =
huawei_ggsn_gw_201610.ne_name) AND (huawei_ggsn_201610.ggsn_function =
huawei_ggsn_gw_201610.ggsn_function) AND (huawei_ggsn_201610.start_time =
huawei_
ggsn_gw_201610.start_time) AND (huawei_ggsn_201610.interval_seconds =
huawei_ggsn_gw_201610.interval_seconds) AND (huawei_ggsn_201610.device_id =
huawei_ggsn_gw_201610.device_id) AND (huawei_ggsn_201610.c_134710251 =
huawei_ggs
n_gw_201610.c_134710251) AND (huawei_ggsn_201610.c_134710252 =
huawei_ggsn_gw_201610.c_134710252) AND (huawei_ggsn_201610.c_134710253 =
huawei_ggsn_gw_201610.c_134710253) AND (huawei_ggsn_201610.ne_id =
huawei_ggsn_gw_201610.ne
_id) AND (huawei_ggsn_201610.ugw_function = huawei_ggsn_gw_201610.ugw_function))
Filter:
((COALESCE(huawei_ggsn_201610.start_time, huawei_ggsn_gw_201610.start_time) >=
'2019-10-01 00:00:00-11'::timestamp with time zone) AND
(COALESCE(huawei_ggsn_201610.start_time, huawei_ggs
n_gw_201610.start_time) < '2019-10-02 00:00:00-11'::timestamp with time zone))
-> Seq Scan on huawei_ggsn_201610
(cost=0.00..255.44 rows=744 width=94)
-> Hash (cost=20.44..20.44 rows=744 width=94)
-> Seq Scan on huawei_ggsn_gw_201610
(cost=0.00..20.44 rows=744 width=94)
[...]
I'm suspecting this; is it useful to test with this commit reverted ?
commit 8edd0e79460b414b1d971895312e549e95e12e4f
Author: Tom Lane <[email protected]>
Date: Mon Mar 25 15:42:35 2019 -0400
Suppress Append and MergeAppend plan nodes that have a single child.
