explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S4J3

Settings

Optimization(s) for this plan:

# 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