#	exclusive	inclusive	rows x	rows	loops	node
1.	4247.801	19359.298	↓ 1.0	1000000	1	→ Merge Join (cost=1311774.16..1332195.77 rows=998405 width=566) (actual time=14697.013..19359.298 rows=1000000 loops=1) Merge Cond: ((tmp_ap.fs_party)::text = (tmp_di.fs_party)::text)
2.	12614.050	14176.923	↓ 1.0	1000000	1	→ Sort (cost=1305391.01..1307887.03 rows=998405 width=533) (actual time=13804.476..14176.923 rows=1000000 loops=1) Sort Key: tmp_ap.fs_party Sort Method: external sort Disk: 306952kB
3.	582.491	1562.873	↓ 1.0	1000000	1	→ Hash Join (cost=2112.40..250389.62 rows=998405 width=533) (actual time=26.381..1562.873 rows=1000000 loops=1) Hash Cond: ((cm_ledgeritem.salesteam)::text = (tmp_ap.tril_gid)::text)
4.	954.031	954.031	↓ 1.0	1000000	1	→ Seq Scan on cm_ledgeritem (cost=0.00..99075.12 rows=998405 width=500) (actual time=0.010..954.031 rows=1000000 loops=1) Filter: (fs_batchid = 1)
5.	19.173	26.351	↑ 1.0	45351	1	→ Hash (cost=1013.51..1013.51 rows=45351 width=66) (actual time=26.351..26.351 rows=45351 loops=1)
6.	7.178	7.178	↑ 1.0	45351	1	→ Seq Scan on tmp_ap (cost=0.00..1013.51 rows=45351 width=66) (actual time=0.002..7.178 rows=45351 loops=1)
7.	84.354	934.574	↓ 22.9	1038059	1	→ Materialize (cost=6383.15..6950.04 rows=45351 width=66) (actual time=699.326..934.574 rows=1038059 loops=1)
8.	845.338	850.220	↑ 1.2	38060	1	→ Sort (cost=6383.15..6496.53 rows=45351 width=66) (actual time=699.320..850.220 rows=38060 loops=1) Sort Key: tmp_di.fs_party Sort Method: external merge Disk: 3344kB
9.	4.882	4.882	↑ 1.0	45351	1	→ Seq Scan on tmp_di (cost=0.00..1013.51 rows=45351 width=66) (actual time=0.005..4.882 rows=45351 loops=1)

"Merge Join  (cost=1311774.16..1332195.77 rows=998405 width=566) (actual time=14697.013..19359.298 rows=1000000 loops=1)"
"  Merge Cond: ((tmp_ap.fs_party)::text = (tmp_di.fs_party)::text)"
"  ->  Sort  (cost=1305391.01..1307887.03 rows=998405 width=533) (actual time=13804.476..14176.923 rows=1000000 loops=1)"
"        Sort Key: tmp_ap.fs_party"
"        Sort Method:  external sort  Disk: 306952kB"
"        ->  Hash Join  (cost=2112.40..250389.62 rows=998405 width=533) (actual time=26.381..1562.873 rows=1000000 loops=1)"
"              Hash Cond: ((cm_ledgeritem.salesteam)::text = (tmp_ap.tril_gid)::text)"
"              ->  Seq Scan on cm_ledgeritem  (cost=0.00..99075.12 rows=998405 width=500) (actual time=0.010..954.031 rows=1000000 loops=1)"
"                    Filter: (fs_batchid = 1)"
"              ->  Hash  (cost=1013.51..1013.51 rows=45351 width=66) (actual time=26.351..26.351 rows=45351 loops=1)"
"                    ->  Seq Scan on tmp_ap  (cost=0.00..1013.51 rows=45351 width=66) (actual time=0.002..7.178 rows=45351 loops=1)"
"  ->  Materialize  (cost=6383.15..6950.04 rows=45351 width=66) (actual time=699.326..934.574 rows=1038059 loops=1)"
"        ->  Sort  (cost=6383.15..6496.53 rows=45351 width=66) (actual time=699.320..850.220 rows=38060 loops=1)"
"              Sort Key: tmp_di.fs_party"
"              Sort Method:  external merge  Disk: 3344kB"
"              ->  Seq Scan on tmp_di  (cost=0.00..1013.51 rows=45351 width=66) (actual time=0.005..4.882 rows=45351 loops=1)"
"Total runtime: 19530.821 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	1	19.173 ms	0.1 %
Hash Join	1	582.491 ms	3.0 %
Materialize	1	84.354 ms	0.4 %
Merge Join	1	4247.801 ms	21.9 %
Seq Scan	3	966.091 ms	5.0 %
Sort	2	13459.388 ms	69.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
cm_ledgeritem	1	954.031 ms	4.9 %
Seq Scan	1	954.031 ms	100.0 %
tmp_ap	1	7.178 ms	0.0 %
Seq Scan	1	7.178 ms	100.0 %
tmp_di	1	4.882 ms	0.0 %
Seq Scan	1	4.882 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: lLU

Colorize:

Columns visibility:

Per node type stats

Per table stats