From: Hans Buschmann <buschmann@nidsa.net>
To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Missing constant propagation in planner on hash quals causes join
 slowdown
Thread-Topic: Missing constant propagation in planner on hash quals causes
 join slowdown
Thread-Index: AQHVhckj7hSZQFlHhEasqG+CcUHWNg==
Date: Fri, 18 Oct 2019 15:40:34 +0000
Message-ID: <1571413123735.26467@nidsa.net>
Accept-Language: de-DE, en-US
Content-Language: de-DE
Content-Type: multipart/mixed; boundary="_002_157141312373526467nidsanet_"
MIME-Version: 1.0
Precedence: bulk

--_002_157141312373526467nidsanet_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Hackers,=0A=
=0A=
By optimising our application I stumbled over the join quals used very ofte=
n in our application.=0A=
In general this concerns datasets, which are subdivided into chunks, like y=
ears, seasons (here half a year), multiple tenants in OLTP system etc.=0A=
In these cases many tables are joined only to data of the same chunk, ident=
ified always by a separating column in the table (here: xx_season).=0A=
=0A=
(I tested it on PG 12.0 on Windows 64 bit, but similar results on older sta=
ble releases and other OS)=0A=
=0A=
Here is the test case, also in the attached file:=0A=
(I choose to join 2 tables with 2 seasons (2 and 3) of about 1 million of r=
ecords for every season. I put some randomness in the table creation to sim=
ulate the normal situation in OLTP systems)=0A=
=0A=
----------------------------------- Source start=0A=
=0A=
drop table if exists tmaster;=0A=
=0A=
create table tmaster (=0A=
id_t1 integer,=0A=
t1_season integer,=0A=
t1_id_t2 integer,=0A=
t1_value integer,=0A=
t1_cdescr varchar,=0A=
primary key (id_t1)=0A=
);=0A=
=0A=
--=0A=
=0A=
=0A=
select setseed (0.34512);=0A=
=0A=
insert into tmaster=0A=
select=0A=
 inum=0A=
,iseason=0A=
,row_number () over () as irow=0A=
,irandom=0A=
,'TXT: '||irandom::varchar=0A=
from (=0A=
select =0A=
 inum::integer=0A=
,((inum>>20)+2)::integer as iseason=0A=
,inum::integer + (500000*random())::integer as irandom=0A=
from generate_series (1,(1<<21)) as inum=0A=
order by irandom=0A=
)qg=0A=
;=0A=
=0A=
alter table tmaster add constraint uk_master_season_id unique (t1_season,id=
_t1);=0A=
=0A=
=0A=
=0A=
drop table if exists tfact;=0A=
=0A=
create table tfact (=0A=
id_t2 integer,=0A=
t2_season integer,=0A=
t2_value integer,=0A=
t2_cdescr varchar,=0A=
primary key (id_t2)=0A=
);=0A=
=0A=
--=0A=
=0A=
=0A=
select setseed (-0.76543);=0A=
=0A=
insert into tfact=0A=
select=0A=
 qg.*=0A=
,'FKT: '||irandom::varchar=0A=
from (=0A=
select =0A=
 inum::integer=0A=
,((inum>>20)+2)::integer as iseason=0A=
,inum::integer + (500000*random())::integer as irandom=0A=
from generate_series (1,(1<<21)) as inum=0A=
order by irandom=0A=
)qg=0A=
;=0A=
=0A=
alter table tfact add constraint uk_fact_season_id unique (t2_season,id_t2)=
;=0A=
=0A=
-----------------=0A=
=0A=
-- slower:=0A=
=0A=
explain (analyze, verbose, costs, settings, buffers)=0A=
select *=0A=
from tmaster=0A=
left join tfact on id_t2=3Dt1_id_t2 and t2_season=3Dt1_season=0A=
where t1_season=3D3=0A=
;=0A=
=0A=
-- faster by setting a constant in left join on condition:=0A=
=0A=
explain (analyze, verbose, costs, settings, buffers)=0A=
select *=0A=
from tmaster=0A=
left join tfact on id_t2=3Dt1_id_t2 and t2_season=3D3 --t1_season=0A=
where t1_season=3D3=0A=
;=0A=
=0A=
----------------------------------- Source end=0A=
=0A=
The results for the first query:=0A=
=0A=
explain (analyze, verbose, costs, settings, buffers)=0A=
select *=0A=
from tmaster=0A=
left join tfact on id_t2=3Dt1_id_t2 and t2_season=3Dt1_season=0A=
where t1_season=3D3=0A=
;=0A=
=0A=
                                                                           =
 QUERY PLAN=0A=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------=0A=
 Hash Left Join  (cost=3D53436.01..111610.15 rows=3D1046129 width=3D52) (ac=
tual time=3D822.784..2476.573 rows=3D1048576 loops=3D1)=0A=
   Output: tmaster.id_t1, tmaster.t1_season, tmaster.t1_id_t2, tmaster.t1_v=
alue, tmaster.t1_cdescr, tfact.id_t2, tfact.t2_season, tfact.t2_value, tfac=
t.t2_cdescr=0A=
   Inner Unique: true=0A=
   Hash Cond: ((tmaster.t1_season =3D tfact.t2_season) AND (tmaster.t1_id_t=
2 =3D tfact.id_t2))=0A=
   Buffers: shared hit=3D2102193, temp read=3D10442 written=3D10442=0A=
   ->  Index Scan using uk_master_season_id on public.tmaster  (cost=3D0.43=
..32263.38 rows=3D1046129 width=3D28) (actual time=3D0.008..565.222 rows=3D=
1048576 loops=3D1)=0A=
         Output: tmaster.id_t1, tmaster.t1_season, tmaster.t1_id_t2, tmaste=
r.t1_value, tmaster.t1_cdescr=0A=
         Index Cond: (tmaster.t1_season =3D 3)=0A=
         Buffers: shared hit=3D1051086=0A=
   ->  Hash  (cost=3D31668.49..31668.49 rows=3D1043473 width=3D24) (actual =
time=3D820.960..820.961 rows=3D1048576 loops=3D1)=0A=
         Output: tfact.id_t2, tfact.t2_season, tfact.t2_value, tfact.t2_cde=
scr=0A=
         Buckets: 524288 (originally 524288)  Batches: 4 (originally 2)  Me=
mory Usage: 28673kB=0A=
         Buffers: shared hit=3D1051107, temp written=3D4316=0A=
         ->  Index Scan using uk_fact_season_id on public.tfact  (cost=3D0.=
43..31668.49 rows=3D1043473 width=3D24) (actual time=3D0.024..598.648 rows=
=3D1048576 loops=3D1)=0A=
               Output: tfact.id_t2, tfact.t2_season, tfact.t2_value, tfact.=
t2_cdescr=0A=
               Index Cond: (tfact.t2_season =3D 3)=0A=
               Buffers: shared hit=3D1051107=0A=
 Settings: effective_cache_size =3D '8GB', random_page_cost =3D '1', temp_b=
uffers =3D '32MB', work_mem =3D '32MB'=0A=
 Planning Time: 0.627 ms=0A=
 Execution Time: 2502.702 ms=0A=
(20 rows)=0A=
=0A=
and for the second one:=0A=
=0A=
explain (analyze, verbose, costs, settings, buffers)=0A=
select *=0A=
from tmaster=0A=
left join tfact on id_t2=3Dt1_id_t2 and t2_season=3D3 --t1_season=0A=
where t1_season=3D3=0A=
;=0A=
=0A=
                                                                           =
 QUERY PLAN=0A=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------=0A=
 Hash Left Join  (cost=3D50827.33..106255.38 rows=3D1046129 width=3D52) (ac=
tual time=3D758.086..2313.175 rows=3D1048576 loops=3D1)=0A=
   Output: tmaster.id_t1, tmaster.t1_season, tmaster.t1_id_t2, tmaster.t1_v=
alue, tmaster.t1_cdescr, tfact.id_t2, tfact.t2_season, tfact.t2_value, tfac=
t.t2_cdescr=0A=
   Inner Unique: true=0A=
   Hash Cond: (tmaster.t1_id_t2 =3D tfact.id_t2)=0A=
   Buffers: shared hit=3D2102193, temp read=3D9024 written=3D9024=0A=
   ->  Index Scan using uk_master_season_id on public.tmaster  (cost=3D0.43=
..32263.38 rows=3D1046129 width=3D28) (actual time=3D0.009..549.793 rows=3D=
1048576 loops=3D1)=0A=
         Output: tmaster.id_t1, tmaster.t1_season, tmaster.t1_id_t2, tmaste=
r.t1_value, tmaster.t1_cdescr=0A=
         Index Cond: (tmaster.t1_season =3D 3)=0A=
         Buffers: shared hit=3D1051086=0A=
   ->  Hash  (cost=3D31668.49..31668.49 rows=3D1043473 width=3D24) (actual =
time=3D756.125..756.125 rows=3D1048576 loops=3D1)=0A=
         Output: tfact.id_t2, tfact.t2_season, tfact.t2_value, tfact.t2_cde=
scr=0A=
         Buckets: 524288  Batches: 4  Memory Usage: 18711kB=0A=
         Buffers: shared hit=3D1051107, temp written=3D4317=0A=
         ->  Index Scan using uk_fact_season_id on public.tfact  (cost=3D0.=
43..31668.49 rows=3D1043473 width=3D24) (actual time=3D0.025..584.652 rows=
=3D1048576 loops=3D1)=0A=
               Output: tfact.id_t2, tfact.t2_season, tfact.t2_value, tfact.=
t2_cdescr=0A=
               Index Cond: (tfact.t2_season =3D 3)=0A=
               Buffers: shared hit=3D1051107=0A=
 Settings: effective_cache_size =3D '8GB', random_page_cost =3D '1', temp_b=
uffers =3D '32MB', work_mem =3D '32MB'=0A=
 Planning Time: 0.290 ms=0A=
 Execution Time: 2339.651 ms=0A=
(20 rows)=0A=
=0A=
By replacing the =3Dt1_season with =3D3 the query took about 160 ms less or=
 about 7 percent.=0A=
=0A=
Both queries are logically equivalent. The planner correctly identifies the=
 Index Cond: (tfact.t2_season =3D 3) for selecting from the index uk_fact_s=
eason_id.=0A=
But in the slower query the outer hash condition still hashes with the colu=
mn t1_season and t2_season as in=0A=
Hash Cond: ((tmaster.t1_season =3D tfact.t2_season) AND (tmaster.t1_id_t2 =
=3D tfact.id_t2)).=0A=
This can only be detected with explain analyze verbose when the hash cond a=
re shown.=0A=
=0A=
The first query notation with and t2_season=3Dt1_season is much more natura=
l, as it requires only one numerical constant to get good query Speed (ofte=
n many fact tables are joined).=0A=
=0A=
The inclusion of the xx_season quals reduces the processed dataset and help=
s also when the seasons columns are used for list partitioning of all the i=
nvolved tables.=0A=
When omitting it, the whole fact table will be joined.=0A=
=0A=
To me it seems that the "constantness" is not propagated to all equivalence=
 columns and not considered in hash joining.=0A=
=0A=
Unfortunely I am not in the position to write a patch, so I would appreciat=
e any help to get this optimization realized.=0A=
=0A=
Much thanks in advance=0A=
=0A=
Hans Buschmann=0A=
=0A=
=0A=

--_002_157141312373526467nidsanet_
Content-Type: text/plain; name="jointest.sql"
Content-Description: jointest.sql
Content-Disposition: attachment; filename="jointest.sql"; size=1807;
	creation-date="Fri, 18 Oct 2019 15:36:37 GMT";
	modification-date="Fri, 18 Oct 2019 15:36:37 GMT"
Content-Transfer-Encoding: base64

DQoNCi0tIGpvaW50ZXN0LnNxbA0KIA0KLS0gMTguMTAuMjAxOQ0KIA0KZHJvcCB0YWJsZSBpZiBl
eGlzdHMgdG1hc3RlcjsNCg0KY3JlYXRlIHRhYmxlIHRtYXN0ZXIgKA0KaWRfdDEgaW50ZWdlciwN
CnQxX3NlYXNvbiBpbnRlZ2VyLA0KdDFfaWRfdDIgaW50ZWdlciwNCnQxX3ZhbHVlIGludGVnZXIs
DQp0MV9jZGVzY3IgdmFyY2hhciwNCnByaW1hcnkga2V5IChpZF90MSkNCik7DQoNCi0tDQoNCg0K
c2VsZWN0IHNldHNlZWQgKDAuMzQ1MTIpOw0KDQppbnNlcnQgaW50byB0bWFzdGVyDQpzZWxlY3QN
CiBpbnVtDQosaXNlYXNvbg0KLHJvd19udW1iZXIgKCkgb3ZlciAoKSBhcyBpcm93DQosaXJhbmRv
bQ0KLCdUWFQ6ICd8fGlyYW5kb206OnZhcmNoYXINCmZyb20gKA0Kc2VsZWN0IA0KIGludW06Omlu
dGVnZXINCiwoKGludW0+PjIwKSsyKTo6aW50ZWdlciBhcyBpc2Vhc29uDQosaW51bTo6aW50ZWdl
ciArICg1MDAwMDAqcmFuZG9tKCkpOjppbnRlZ2VyIGFzIGlyYW5kb20NCmZyb20gZ2VuZXJhdGVf
c2VyaWVzICgxLCgxPDwyMSkpIGFzIGludW0NCm9yZGVyIGJ5IGlyYW5kb20NCilxZw0KLS0gbGlt
aXQgNTANCjsNCg0KYWx0ZXIgdGFibGUgdG1hc3RlciBhZGQgY29uc3RyYWludCB1a19tYXN0ZXJf
c2Vhc29uX2lkIHVuaXF1ZSAodDFfc2Vhc29uLGlkX3QxKTsNCg0KDQoNCmRyb3AgdGFibGUgaWYg
ZXhpc3RzIHRmYWN0Ow0KDQpjcmVhdGUgdGFibGUgdGZhY3QgKA0KaWRfdDIgaW50ZWdlciwNCnQy
X3NlYXNvbiBpbnRlZ2VyLA0KdDJfdmFsdWUgaW50ZWdlciwNCnQyX2NkZXNjciB2YXJjaGFyLA0K
cHJpbWFyeSBrZXkgKGlkX3QyKQ0KKTsNCg0KLS0NCg0KDQpzZWxlY3Qgc2V0c2VlZCAoLTAuNzY1
NDMpOw0KDQppbnNlcnQgaW50byB0ZmFjdA0Kc2VsZWN0DQogcWcuKg0KLCdGS1Q6ICd8fGlyYW5k
b206OnZhcmNoYXINCmZyb20gKA0Kc2VsZWN0IA0KIGludW06OmludGVnZXINCiwoKGludW0+PjIw
KSsyKTo6aW50ZWdlciBhcyBpc2Vhc29uDQosaW51bTo6aW50ZWdlciArICg1MDAwMDAqcmFuZG9t
KCkpOjppbnRlZ2VyIGFzIGlyYW5kb20NCmZyb20gZ2VuZXJhdGVfc2VyaWVzICgxLCgxPDwyMSkp
IGFzIGludW0NCm9yZGVyIGJ5IGlyYW5kb20NCilxZw0KLS0gbGltaXQgNTANCjsNCg0KYWx0ZXIg
dGFibGUgdGZhY3QgYWRkIGNvbnN0cmFpbnQgdWtfZmFjdF9zZWFzb25faWQgdW5pcXVlICh0Ml9z
ZWFzb24saWRfdDIpOw0KDQotLS0tLS0tLS0tLS0tLS0tLQ0KDQotLSBzbG93ZXI6DQoNCmV4cGxh
aW4gKGFuYWx5emUsIHZlcmJvc2UsIGNvc3RzLCBzZXR0aW5ncywgYnVmZmVycykNCnNlbGVjdCAq
DQpmcm9tIHRtYXN0ZXINCmxlZnQgam9pbiB0ZmFjdCBvbiBpZF90Mj10MV9pZF90MiBhbmQgdDJf
c2Vhc29uPXQxX3NlYXNvbg0Kd2hlcmUgdDFfc2Vhc29uPTMNCjsNCg0KLS0gZmFzdGVyIGJ5IHNl
dHRpbmcgYSBjb25zdGFudCBpbiBsZWZ0IGpvaW4gb24gY29uZGl0aW9uOg0KDQpleHBsYWluIChh
bmFseXplLCB2ZXJib3NlLCBjb3N0cywgc2V0dGluZ3MsIGJ1ZmZlcnMpDQpzZWxlY3QgKg0KZnJv
bSB0bWFzdGVyDQpsZWZ0IGpvaW4gdGZhY3Qgb24gaWRfdDI9dDFfaWRfdDIgYW5kIHQyX3NlYXNv
bj0zIC0tdDFfc2Vhc29uDQp3aGVyZSB0MV9zZWFzb249Mw0KOw0KDQoNCg0KDQoNCg0KDQoNCg0K
DQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoN
Cg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0K
DQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KDQoNCg0KIA0KIA==

--_002_157141312373526467nidsanet_--