#	exclusive	inclusive	rows x	rows	loops	node
1.	4,793.112	225,609.002	↑ 6,462.1	766	1	→ Unique (cost=20,706,568.31..21,380,228.24 rows=4,950,000 width=33) (actual time=202,926.471..225,609.002 rows=766 loops=1)
2.						Initplan (forUnique)
3.	0.000	913.340	↑ 1.0	1	1	→ Finalize Aggregate (cost=144,759.54..144,759.55 rows=1 width=32) (actual time=913.340..913.340 rows=1 loops=1)
4.	21.229	913.371	↓ 1.5	3	1	→ Gather (cost=144,759.33..144,759.54 rows=2 width=32) (actual time=913.027..913.371 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2
5.	260.300	892.142	↑ 1.0	1	3	→ Partial Aggregate (cost=143,759.33..143,759.34 rows=1 width=32) (actual time=892.142..892.142 rows=1 loops=3)
6.	631.842	631.842	↑ 1.2	2,000,405	3	→ Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..137,508.06 rows=2,500,506 width=4) (actual time=0.056..631.842 rows=2,000,405 loops=3)
7.	0.000	904.803	↑ 1.0	1	1	→ Finalize Aggregate (cost=144,759.54..144,759.55 rows=1 width=32) (actual time=904.803..904.803 rows=1 loops=1)
8.	21.568	904.820	↓ 1.5	3	1	→ Gather (cost=144,759.33..144,759.54 rows=2 width=32) (actual time=904.522..904.820 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2
9.	271.601	883.252	↑ 1.0	1	3	→ Partial Aggregate (cost=143,759.33..143,759.34 rows=1 width=32) (actual time=883.252..883.252 rows=1 loops=3)
10.	611.651	611.651	↑ 1.2	2,000,405	3	→ Parallel Seq Scan on lineitem lineitem_2 (cost=0.00..137,508.06 rows=2,500,506 width=4) (actual time=0.055..611.651 rows=2,000,405 loops=3)
11.	184,083.185	218,997.747	↑ 2.9	31,413,722	1	→ Sort (cost=20,417,049.21..20,641,602.52 rows=89,821,324 width=33) (actual time=202,926.468..218,997.747 rows=31,413,722 loops=1) Sort Key: top_customers.c_name, top_customers.c_acctbal Sort Method: external merge Disk: 1350992kB
12.	4,079.824	34,914.562	↑ 2.9	31,413,722	1	→ Nested Loop (cost=43,537.46..1,183,238.21 rows=89,821,324 width=33) (actual time=2,167.319..34,914.562 rows=31,413,722 loops=1)
13.	20.872	144.094	↑ 1.1	40,171	1	→ Unique (cost=13,069.51..14,180.61 rows=44,444 width=130) (actual time=99.595..144.094 rows=40,171 loops=1)
14.	53.664	123.222	↑ 1.1	40,171	1	→ Sort (cost=13,069.51..13,180.62 rows=44,444 width=130) (actual time=99.594..123.222 rows=40,171 loops=1) Sort Key: part.p_partkey, part.p_name, part.p_mfgr, part.p_brand, part.p_type, part.p_size, part.p_container, part.p_retailprice, part.p_comment Sort Method: external sort Disk: 5600kB
15.	69.558	69.558	↑ 1.1	40,171	1	→ Seq Scan on part (cost=0.00..6,597.00 rows=44,444 width=130) (actual time=0.090..69.558 rows=40,171 loops=1) Filter: ((p_type)::text ~~ '%TIN%'::text) Rows Removed by Filter: 159829
16.	1,288.524	30,690.644	↑ 2.6	782	40,171	→ Materialize (cost=30,467.95..45,851.66 rows=2,021 width=33) (actual time=0.052..0.764 rows=782 loops=40,171)
17.	3.178	29,402.120	↑ 2.6	782	1	→ Merge Join (cost=30,467.95..45,841.56 rows=2,021 width=33) (actual time=2,067.714..29,402.120 rows=782 loops=1) Merge Cond: (top_customers.c_custkey = orders.o_orderkey)
18.	9.476	29,389.065	↑ 10.3	782	1	→ Merge Join (cost=30,367.28..44,012.49 rows=8,043 width=41) (actual time=2,067.680..29,389.065 rows=782 loops=1) Merge Cond: (lineitem.l_orderkey = top_customers.c_custkey)
19.	4.467	29,112.986	↑ 38.1	1,593	1	→ Nested Loop (cost=0.86..551,580.87 rows=60,618 width=12) (actual time=1,818.215..29,112.986 rows=1,593 loops=1)
20.	1,880.963	1,880.963	↑ 38.1	1,593	1	→ Index Scan using lineitem_pkey on lineitem (cost=0.43..446,017.86 rows=60,618 width=20) (actual time=1,818.186..1,880.963 rows=1,593 loops=1) Filter: ((l_tax = $1) AND (l_discount = $3)) Rows Removed by Filter: 148815
21.	27,227.556	27,227.556	↑ 1.0	1	1,593	→ Index Only Scan using partsupp_pkey on partsupp (cost=0.42..1.74 rows=1 width=8) (actual time=17.092..17.092 rows=1 loops=1,593) Index Cond: ((ps_partkey = lineitem.l_partkey) AND (ps_suppkey = lineitem.l_suppkey)) Heap Fetches: 1593
22.	85.681	266.603	↓ 1.5	74,782	1	→ Sort (cost=30,365.31..30,490.31 rows=50,000 width=29) (actual time=249.458..266.603 rows=74,782 loops=1) Sort Key: top_customers.c_custkey Sort Method: external sort Disk: 3368kB
23.	9.601	180.922	↓ 1.5	74,766	1	→ Subquery Scan on top_customers (cost=25,837.90..26,462.90 rows=50,000 width=29) (actual time=156.593..180.922 rows=74,766 loops=1)
24.	39.181	171.321	↓ 1.5	74,766	1	→ Sort (cost=25,837.90..25,962.90 rows=50,000 width=491) (actual time=156.589..171.321 rows=74,766 loops=1) Sort Key: customer.c_acctbal DESC Sort Method: external merge Disk: 2928kB
25.						Initplan (forSort)
26.	0.000	39.368	↑ 1.0	1	1	→ Finalize Aggregate (cost=5,366.47..5,366.48 rows=1 width=32) (actual time=39.368..39.368 rows=1 loops=1)
27.	20.878	39.373	↓ 1.5	3	1	→ Gather (cost=5,366.25..5,366.46 rows=2 width=32) (actual time=39.070..39.373 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2
28.	7.673	18.495	↑ 1.0	1	3	→ Partial Aggregate (cost=4,366.25..4,366.26 rows=1 width=32) (actual time=18.494..18.495 rows=1 loops=3)
29.	10.822	10.822	↑ 1.2	50,000	3	→ Parallel Seq Scan on customer customer_1 (cost=0.00..4,210.00 rows=62,500 width=6) (actual time=0.014..10.822 rows=50,000 loops=3)
30.	92.772	92.772	↓ 1.5	74,766	1	→ Seq Scan on customer (cost=0.00..5,460.00 rows=50,000 width=491) (actual time=39.396..92.772 rows=74,766 loops=1) Filter: (c_acctbal > $5) Rows Removed by Filter: 75234
31.	9.877	9.877	↑ 40.1	37,382	1	→ Index Only Scan using orders_pkey on orders (cost=0.43..65,062.43 rows=1,500,000 width=4) (actual time=0.029..9.877 rows=37,382 loops=1) Heap Fetches: 37382

"Unique  (cost=20706568.31..21380228.24 rows=4950000 width=33) (actual time=202926.471..225609.002 rows=766 loops=1)"
"  InitPlan 1 (returns $1)"
"    ->  Finalize Aggregate  (cost=144759.54..144759.55 rows=1 width=32) (actual time=913.340..913.340 rows=1 loops=1)"
"          ->  Gather  (cost=144759.33..144759.54 rows=2 width=32) (actual time=913.027..913.371 rows=3 loops=1)"
"                Workers Planned: 2"
"                Workers Launched: 2"
"                ->  Partial Aggregate  (cost=143759.33..143759.34 rows=1 width=32) (actual time=892.142..892.142 rows=1 loops=3)"
"                      ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..137508.06 rows=2500506 width=4) (actual time=0.056..631.842 rows=2000405 loops=3)"
"  InitPlan 2 (returns $3)"
"    ->  Finalize Aggregate  (cost=144759.54..144759.55 rows=1 width=32) (actual time=904.803..904.803 rows=1 loops=1)"
"          ->  Gather  (cost=144759.33..144759.54 rows=2 width=32) (actual time=904.522..904.820 rows=3 loops=1)"
"                Workers Planned: 2"
"                Workers Launched: 2"
"                ->  Partial Aggregate  (cost=143759.33..143759.34 rows=1 width=32) (actual time=883.252..883.252 rows=1 loops=3)"
"                      ->  Parallel Seq Scan on lineitem lineitem_2  (cost=0.00..137508.06 rows=2500506 width=4) (actual time=0.055..611.651 rows=2000405 loops=3)"
"  ->  Sort  (cost=20417049.21..20641602.52 rows=89821324 width=33) (actual time=202926.468..218997.747 rows=31413722 loops=1)"
"        Sort Key: top_customers.c_name, top_customers.c_acctbal"
"        Sort Method: external merge  Disk: 1350992kB"
"        ->  Nested Loop  (cost=43537.46..1183238.21 rows=89821324 width=33) (actual time=2167.319..34914.562 rows=31413722 loops=1)"
"              ->  Unique  (cost=13069.51..14180.61 rows=44444 width=130) (actual time=99.595..144.094 rows=40171 loops=1)"
"                    ->  Sort  (cost=13069.51..13180.62 rows=44444 width=130) (actual time=99.594..123.222 rows=40171 loops=1)"
"                          Sort Key: part.p_partkey, part.p_name, part.p_mfgr, part.p_brand, part.p_type, part.p_size, part.p_container, part.p_retailprice, part.p_comment"
"                          Sort Method: external sort  Disk: 5600kB"
"                          ->  Seq Scan on part  (cost=0.00..6597.00 rows=44444 width=130) (actual time=0.090..69.558 rows=40171 loops=1)"
"                                Filter: ((p_type)::text ~~ '%TIN%'::text)"
"                                Rows Removed by Filter: 159829"
"              ->  Materialize  (cost=30467.95..45851.66 rows=2021 width=33) (actual time=0.052..0.764 rows=782 loops=40171)"
"                    ->  Merge Join  (cost=30467.95..45841.56 rows=2021 width=33) (actual time=2067.714..29402.120 rows=782 loops=1)"
"                          Merge Cond: (top_customers.c_custkey = orders.o_orderkey)"
"                          ->  Merge Join  (cost=30367.28..44012.49 rows=8043 width=41) (actual time=2067.680..29389.065 rows=782 loops=1)"
"                                Merge Cond: (lineitem.l_orderkey = top_customers.c_custkey)"
"                                ->  Nested Loop  (cost=0.86..551580.87 rows=60618 width=12) (actual time=1818.215..29112.986 rows=1593 loops=1)"
"                                      ->  Index Scan using lineitem_pkey on lineitem  (cost=0.43..446017.86 rows=60618 width=20) (actual time=1818.186..1880.963 rows=1593 loops=1)"
"                                            Filter: ((l_tax = $1) AND (l_discount = $3))"
"                                            Rows Removed by Filter: 148815"
"                                      ->  Index Only Scan using partsupp_pkey on partsupp  (cost=0.42..1.74 rows=1 width=8) (actual time=17.092..17.092 rows=1 loops=1593)"
"                                            Index Cond: ((ps_partkey = lineitem.l_partkey) AND (ps_suppkey = lineitem.l_suppkey))"
"                                            Heap Fetches: 1593"
"                                ->  Sort  (cost=30365.31..30490.31 rows=50000 width=29) (actual time=249.458..266.603 rows=74782 loops=1)"
"                                      Sort Key: top_customers.c_custkey"
"                                      Sort Method: external sort  Disk: 3368kB"
"                                      ->  Subquery Scan on top_customers  (cost=25837.90..26462.90 rows=50000 width=29) (actual time=156.593..180.922 rows=74766 loops=1)"
"                                            ->  Sort  (cost=25837.90..25962.90 rows=50000 width=491) (actual time=156.589..171.321 rows=74766 loops=1)"
"                                                  Sort Key: customer.c_acctbal DESC"
"                                                  Sort Method: external merge  Disk: 2928kB"
"                                                  InitPlan 3 (returns $5)"
"                                                    ->  Finalize Aggregate  (cost=5366.47..5366.48 rows=1 width=32) (actual time=39.368..39.368 rows=1 loops=1)"
"                                                          ->  Gather  (cost=5366.25..5366.46 rows=2 width=32) (actual time=39.070..39.373 rows=3 loops=1)"
"                                                                Workers Planned: 2"
"                                                                Workers Launched: 2"
"                                                                ->  Partial Aggregate  (cost=4366.25..4366.26 rows=1 width=32) (actual time=18.494..18.495 rows=1 loops=3)"
"                                                                      ->  Parallel Seq Scan on customer customer_1  (cost=0.00..4210.00 rows=62500 width=6) (actual time=0.014..10.822 rows=50000 loops=3)"
"                                                  ->  Seq Scan on customer  (cost=0.00..5460.00 rows=50000 width=491) (actual time=39.396..92.772 rows=74766 loops=1)"
"                                                        Filter: (c_acctbal > $5)"
"                                                        Rows Removed by Filter: 75234"
"                          ->  Index Only Scan using orders_pkey on orders  (cost=0.43..65062.43 rows=1500000 width=4) (actual time=0.029..9.877 rows=37382 loops=1)"
"                                Heap Fetches: 37382"
"Planning time: 2.775 ms"
"Execution time: 225756.863 ms"

Per node type stats

node type	count	sum of times	% of query
Finalize Aggregate	3	0.000 ms	0.0 %
Gather	3	63.675 ms	0.0 %
Index Only Scan	2	27,237.433 ms	12.1 %
Index Scan	1	1,880.963 ms	0.8 %
Materialize	1	1,288.524 ms	0.6 %
Merge Join	2	12.654 ms	0.0 %
Nested Loop	2	4,084.291 ms	1.8 %
Parallel Seq Scan	3	1,254.315 ms	0.6 %
Partial Aggregate	3	539.574 ms	0.2 %
Seq Scan	2	162.330 ms	0.1 %
Sort	4	184,261.711 ms	81.7 %
Subquery Scan	1	9.601 ms	0.0 %
Unique	2	4,813.984 ms	2.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
customer	2	103.594 ms	0.0 %
Parallel Seq Scan	1	10.822 ms	10.4 %
Seq Scan	1	92.772 ms	89.6 %
lineitem	3	3,124.456 ms	1.4 %
Index Scan	1	1,880.963 ms	60.2 %
Parallel Seq Scan	2	1,243.493 ms	39.8 %
orders	1	9.877 ms	0.0 %
Index Only Scan	1	9.877 ms	100.0 %
part	1	69.558 ms	0.0 %
Seq Scan	1	69.558 ms	100.0 %
partsupp	1	27,227.556 ms	12.1 %
Index Only Scan	1	27,227.556 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZEC4

Color mode:

Visible columns:

Optimization(s) for this plan:

Per node type stats

Per table stats