explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cr5C

Settings
# exclusive inclusive rows x rows loops node
1. 18,949.814 369,103.483 ↑ 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=348,903.815..369,103.483 rows=1,334,397 loops=1)

2. 8,318.629 350,153.669 ↑ 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=348,903.688..350,153.669 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,674.110 341,835.040 ↑ 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=333,045.230..341,835.040 rows=1,334,397 loops=1)

4. 8,224.081 334,160.930 ↑ 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=333,045.188..334,160.930 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,653.371 325,936.849 ↑ 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=317,047.845..325,936.849 rows=1,334,397 loops=1)

6. 8,385.014 318,283.478 ↑ 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=317,047.793..318,283.478 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,227.436 309,898.464 ↑ 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=299,965.481..309,898.464 rows=1,334,397 loops=1)

8. 10,246.406 302,671.028 ↑ 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=299,965.438..302,671.028 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,992kB
9. 3,362.082 292,424.622 ↑ 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=138,965.099..292,424.622 rows=1,334,397 loops=1)

  • Hash Cond: (f.fact_labor_line_id = flls.fact_labor_line_id)
10. 1,907.630 288,254.257 ↑ 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=138,156.785..288,254.257 rows=1,334,397 loops=1)

  • Join Filter: false
11. 1,530.982 286,346.627 ↑ 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=138,156.776..286,346.627 rows=1,334,397 loops=1)

  • Join Filter: false
12. 1,502.678 284,815.645 ↑ 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=138,156.771..284,815.645 rows=1,334,397 loops=1)

  • Join Filter: false
13. 1,631.674 283,312.967 ↑ 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=138,156.766..283,312.967 rows=1,334,397 loops=1)

  • Join Filter: false
14. 1,187.834 281,681.293 ↑ 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=138,156.758..281,681.293 rows=1,334,397 loops=1)

  • Join Filter: false
15. 1,823.573 280,493.459 ↑ 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=138,156.751..280,493.459 rows=1,334,397 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_7.dim_repair_order_id)
16. 1,971.422 226,613.575 ↑ 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,100.265..226,613.575 rows=1,446,182 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_6.dim_repair_order_id)
17. 32,506.167 183,212.462 ↑ 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=44,670.388..183,212.462 rows=1,499,421 loops=1)

  • Hash Cond: (f.dim_repair_order_id = fll_5.dim_repair_order_id)
18. 143,586.596 143,586.596 ↑ 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=37,550.537..143,586.596 rows=1,499,421 loops=1)

19. 160.661 7,119.699 ↓ 37.6 951,427 1

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

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 45,358kB
20. 1,325.607 6,959.038 ↓ 37.6 951,427 1

HashAggregate (cost=1,297,978.36..1,298,231.19 rows=25,283 width=8) (actual time=6,722.667..6,959.038 rows=951,427 loops=1)

  • Group Key: fll_5.dim_repair_order_id
21. 5,633.431 5,633.431 ↑ 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=0.871..5,633.431 rows=3,255,493 loops=1)

22. 111.868 41,429.691 ↓ 27.3 691,224 1

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

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 35,193kB
23. 867.202 41,317.823 ↓ 27.3 691,224 1

HashAggregate (cost=1,979,417.40..1,979,670.23 rows=25,283 width=8) (actual time=41,156.596..41,317.823 rows=691,224 loops=1)

  • Group Key: fll_6.dim_repair_order_id
24. 40,450.621 40,450.621 ↑ 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,223.790..40,450.621 rows=1,680,723 loops=1)

25. 109.845 52,056.311 ↓ 23.1 584,447 1

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

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 31,022kB
26. 928.688 51,946.466 ↓ 23.1 584,447 1

HashAggregate (cost=2,005,176.03..2,005,428.86 rows=25,283 width=8) (actual time=51,809.111..51,946.466 rows=584,447 loops=1)

  • Group Key: fll_7.dim_repair_order_id
27. 51,017.778 51,017.778 ↑ 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=12,991.867..51,017.778 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.001 808.283 ↓ 0.0 0 1

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

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

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