explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ysb2

Settings
# exclusive inclusive rows x rows loops node
1. 19,017.339 383,357.118 ↑ 3.1 1,334,397 1

WindowAgg (cost=113,372,374.71..114,255,278.63 rows=4,154,842 width=18,084) (actual time=363,054.751..383,357.118 rows=1,334,397 loops=1)

2. 8,573.294 364,339.779 ↑ 3.1 1,334,397 1

Sort (cost=113,372,374.71..113,382,761.81 rows=4,154,842 width=12,120) (actual time=363,054.624..364,339.779 rows=1,334,397 loops=1)

  • Sort Key: f.dim_repair_order_id, f.dim_employee_id_technician, f.pay_type_short, f.fact_labor_line_id
  • Sort Method: external sort Disk: 1,163,520kB
3. 7,824.139 355,766.485 ↑ 3.1 1,334,397 1

WindowAgg (cost=87,569,302.56..87,662,786.50 rows=4,154,842 width=12,120) (actual time=346,650.206..355,766.485 rows=1,334,397 loops=1)

4. 8,366.354 347,942.346 ↑ 3.1 1,334,397 1

Sort (cost=87,569,302.56..87,579,689.66 rows=4,154,842 width=12,112) (actual time=346,650.172..347,942.346 rows=1,334,397 loops=1)

  • Sort Key: f.dim_repair_order_id, f.dim_employee_id_technician, f.fact_labor_line_id
  • Sort Method: external sort Disk: 1,153,072kB
5. 7,519.052 339,575.992 ↑ 3.1 1,334,397 1

WindowAgg (cost=61,782,864.11..61,876,348.05 rows=4,154,842 width=12,112) (actual time=330,645.378..339,575.992 rows=1,334,397 loops=1)

6. 8,464.277 332,056.940 ↑ 3.1 1,334,397 1

Sort (cost=61,782,864.11..61,793,251.21 rows=4,154,842 width=12,104) (actual time=330,645.347..332,056.940 rows=1,334,397 loops=1)

  • Sort Key: f.dim_repair_order_id, f.pay_type_short, f.fact_labor_line_id
  • Sort Method: external sort Disk: 1,142,632kB
7. 7,305.787 323,592.663 ↑ 3.1 1,334,397 1

WindowAgg (cost=36,023,450.56..36,106,547.40 rows=4,154,842 width=12,104) (actual time=313,818.626..323,592.663 rows=1,334,397 loops=1)

8. 10,080.638 316,286.876 ↑ 3.1 1,334,397 1

Sort (cost=36,023,450.56..36,033,837.67 rows=4,154,842 width=12,096) (actual time=313,818.598..316,286.876 rows=1,334,397 loops=1)

  • Sort Key: f.dim_repair_order_id, f.fact_labor_line_id
  • Sort Method: external merge Disk: 1,130,984kB
9. 3,322.051 306,206.238 ↑ 3.1 1,334,397 1

Hash Left Join (cost=6,726,961.89..10,363,767.56 rows=4,154,842 width=12,096) (actual time=145,677.948..306,206.238 rows=1,334,397 loops=1)

  • Hash Cond: (f.fact_labor_line_id = flls.fact_labor_line_id)
10. 1,937.460 301,805.518 ↑ 3.1 1,334,397 1

Nested Loop Left Join (cost=6,726,951.67..10,348,176.64 rows=4,154,842 width=4,520) (actual time=144,599.250..301,805.518 rows=1,334,397 loops=1)

  • Join Filter: false
11. 1,508.809 299,868.058 ↑ 3.1 1,334,397 1

Nested Loop Left Join (cost=6,726,951.67..10,306,628.22 rows=4,154,842 width=2,624) (actual time=144,599.244..299,868.058 rows=1,334,397 loops=1)

  • Join Filter: false
12. 1,512.863 298,359.249 ↑ 3.1 1,334,397 1

Nested Loop Left Join (cost=6,726,951.67..10,265,079.80 rows=4,154,842 width=2,620) (actual time=144,599.239..298,359.249 rows=1,334,397 loops=1)

  • Join Filter: false
13. 1,526.467 296,846.386 ↑ 3.1 1,334,397 1

Nested Loop Left Join (cost=6,726,951.67..10,223,531.38 rows=4,154,842 width=2,616) (actual time=144,599.234..296,846.386 rows=1,334,397 loops=1)

  • Join Filter: false
14. 1,178.818 295,319.919 ↑ 3.1 1,334,397 1

Nested Loop Left Join (cost=6,726,951.67..10,181,982.96 rows=4,154,842 width=720) (actual time=144,599.228..295,319.919 rows=1,334,397 loops=1)

  • Join Filter: false
15. 1,810.312 294,141.101 ↑ 3.1 1,334,397 1

Hash Join (cost=6,726,951.67..10,140,434.54 rows=4,154,842 width=716) (actual time=144,599.221..294,141.101 rows=1,334,397 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_7.dim_repair_order_id)
16. 1,944.480 233,883.546 ↑ 2.9 1,446,182 1

Hash Join (cost=4,720,953.94..8,123,528.53 rows=4,154,842 width=740) (actual time=86,151.910..233,883.546 rows=1,446,182 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_6.dim_repair_order_id)
17. 31,986.757 183,986.865 ↑ 2.8 1,499,421 1

Hash Join (cost=2,740,714.85..6,132,381.15 rows=4,154,842 width=732) (actual time=38,199.638..183,986.865 rows=1,499,421 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_5.dim_repair_order_id)
18. 144,501.286 144,501.286 ↑ 2.8 1,499,421 1

Remote Subquery Scan on all (pr_era_pgxl2_data,pr_era_pgxl3_data) (cost=1,442,014.79..7,851,652.63 rows=4,154,842 width=724) (actual time=30,700.672..144,501.286 rows=1,499,421 loops=1)

19. 150.960 7,498.822 ↓ 37.6 951,427 1

Hash (cost=1,298,484.02..1,298,484.02 rows=25,283 width=8) (actual time=7,498.822..7,498.822 rows=951,427 loops=1)

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 45,358kB
20. 1,334.752 7,347.862 ↓ 37.6 951,427 1

HashAggregate (cost=1,297,978.36..1,298,231.19 rows=25,283 width=8) (actual time=7,112.246..7,347.862 rows=951,427 loops=1)

  • Group Key: fll_5.dim_repair_order_id
21. 6,013.110 6,013.110 ↑ 3.1 3,255,493 1

Remote Subquery Scan on all (pr_era_pgxl2_data,pr_era_pgxl3_data) (cost=100.00..1,272,970.04 rows=10,003,327 width=8) (actual time=1.801..6,013.110 rows=3,255,493 loops=1)

22. 114.972 47,952.201 ↓ 27.3 691,224 1

Hash (cost=1,979,923.06..1,979,923.06 rows=25,283 width=8) (actual time=47,952.201..47,952.201 rows=691,224 loops=1)

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 35,193kB
23. 894.027 47,837.229 ↓ 27.3 691,224 1

HashAggregate (cost=1,979,417.40..1,979,670.23 rows=25,283 width=8) (actual time=47,676.883..47,837.229 rows=691,224 loops=1)

  • Group Key: fll_6.dim_repair_order_id
24. 46,943.202 46,943.202 ↑ 1.4 1,680,723 1

Remote Subquery Scan on all (pr_era_pgxl2_data,pr_era_pgxl3_data) (cost=657,884.40..1,973,634.65 rows=2,313,100 width=8) (actual time=3,143.209..46,943.202 rows=1,680,723 loops=1)

25. 93.434 58,447.243 ↓ 23.1 584,447 1

Hash (cost=2,005,681.69..2,005,681.69 rows=25,283 width=8) (actual time=58,447.243..58,447.243 rows=584,447 loops=1)

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 31,022kB
26. 922.237 58,353.809 ↓ 23.1 584,447 1

HashAggregate (cost=2,005,176.03..2,005,428.86 rows=25,283 width=8) (actual time=58,211.026..58,353.809 rows=584,447 loops=1)

  • Group Key: fll_7.dim_repair_order_id
27. 57,431.572 57,431.572 ↑ 1.4 1,994,730 1

Remote Subquery Scan on all (pr_era_pgxl2_data,pr_era_pgxl3_data) (cost=654,447.93..1,997,984.46 rows=2,876,627 width=8) (actual time=10,185.530..57,431.572 rows=1,994,730 loops=1)

28. 0.000 0.000 ↓ 0.0 0 1,334,397

Result (cost=0.00..0.00 rows=0 width=4) (actual time=0.000..0.000 rows=0 loops=1,334,397)

  • One-Time Filter: false
29. 0.000 0.000 ↓ 0.0 0 1,334,397

Result (cost=0.00..0.00 rows=0 width=1,896) (actual time=0.000..0.000 rows=0 loops=1,334,397)

  • One-Time Filter: false
30. 0.000 0.000 ↓ 0.0 0 1,334,397

Result (cost=0.00..0.00 rows=0 width=4) (actual time=0.000..0.000 rows=0 loops=1,334,397)

  • One-Time Filter: false
31. 0.000 0.000 ↓ 0.0 0 1,334,397

Result (cost=0.00..0.00 rows=0 width=4) (actual time=0.000..0.000 rows=0 loops=1,334,397)

  • One-Time Filter: false
32. 0.000 0.000 ↓ 0.0 0 1,334,397

Result (cost=0.00..0.00 rows=0 width=1,896) (actual time=0.000..0.000 rows=0 loops=1,334,397)

  • One-Time Filter: false
33. 0.002 1,078.669 ↓ 0.0 0 1

Hash (cost=185.99..185.99 rows=10 width=7,584) (actual time=1,078.669..1,078.669 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
34. 1,078.667 1,078.667 ↓ 0.0 0 1

Remote Subquery Scan on all (pr_era_pgxl3_data) (cost=100.00..185.99 rows=10 width=7,584) (actual time=1,078.667..1,078.667 rows=0 loops=1)