explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7CJb

Settings
# exclusive inclusive rows x rows loops node
1. 0.375 1,476.115 ↑ 1.0 1 1

GroupAggregate (cost=3,627,540.51..9,148,967.43 rows=1 width=36) (actual time=1,476.115..1,476.115 rows=1 loops=1)

  • Group Key: roster.id
2. 26.832 1,475.740 ↓ 19.0 19 1

Hash Join (cost=3,627,540.51..9,148,967.21 rows=1 width=140) (actual time=1,347.864..1,475.740 rows=19 loops=1)

  • Hash Cond: (("*SELECT* 1".classification_id = COALESCE(avl.classification_id, wage.classification_id)) AND (("*SELECT* 1".employment_status)::text = (wage.employment_status)::text))
  • Join Filter: ((annotation.day <@ wages_revision.valid_period) AND (wages_classification_3.level <@ "*SELECT* 1".levels) AND (COALESCE(numeric_age(annotation.day, people.dob), '100'::numeric) <@ "*SELECT* 1".age_range))
  • Rows Removed by Join Filter: 235
3. 53.876 483.121 ↑ 2.3 246,892 1

Hash Join (cost=29,373.35..5,546,628.53 rows=555,715 width=229) (actual time=90.064..483.121 rows=246,892 loops=1)

  • Hash Cond: ("*SELECT* 1".condition_id = rules.condition_id)
4. 71.945 373.869 ↓ 14.5 247,166 1

Hash Join (cost=23,615.76..5,415,800.60 rows=17,010 width=201) (actual time=34.649..373.869 rows=247,166 loops=1)

  • Hash Cond: (("*SELECT* 1".revision_id = wages_revision.revision_id) AND ("*SELECT* 1".condition_id = condition.condition_id))
5. 18.397 293.949 ↑ 69.2 247,166 1

Append (cost=17,534.68..5,235,207.52 rows=17,111,000 width=163) (actual time=26.598..293.949 rows=247,166 loops=1)

6. 24.965 216.534 ↑ 68.1 236,812 1

Subquery Scan on *SELECT* 1 (cost=17,534.68..4,910,070.88 rows=16,127,200 width=163) (actual time=26.598..216.534 rows=236,812 loops=1)

7. 123.699 191.569 ↑ 68.1 236,812 1

Merge Join (cost=17,534.68..1,684,630.88 rows=16,127,200 width=163) (actual time=26.598..191.569 rows=236,812 loops=1)

  • Merge Cond: (wages_rate.age_id = wages_age.age_id)
8. 27.671 27.671 ↓ 1.0 169,065 1

Index Scan using wages_rate_a3b35f2c on wages_rate (cost=0.42..36,638.17 rows=161,272 width=18) (actual time=0.006..27.671 rows=169,065 loops=1)

9. 27.098 40.199 ↓ 5.3 169,065 1

Sort (cost=17,534.26..17,613.65 rows=31,756 width=62) (actual time=26.568..40.199 rows=169,065 loops=1)

  • Sort Key: wages_age.age_id
  • Sort Method: quicksort Memory: 6,170kB
10. 9.223 13.101 ↓ 1.0 32,970 1

Hash Join (cost=2,107.26..15,159.75 rows=31,756 width=62) (actual time=1.512..13.101 rows=32,970 loops=1)

  • Hash Cond: (wages_age.classification_id = wages_classification.classification_id)
11. 2.417 2.417 ↓ 1.0 32,970 1

Seq Scan on wages_age (cost=0.00..6,582.20 rows=31,756 width=25) (actual time=0.005..2.417 rows=32,970 loops=1)

12. 0.806 1.461 ↓ 1.1 5,353 1

Hash (cost=1,081.80..1,081.80 rows=5,064 width=41) (actual time=1.461..1.461 rows=5,353 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 464kB
13. 0.655 0.655 ↓ 1.1 5,353 1

Seq Scan on wages_classification (cost=0.00..1,081.80 rows=5,064 width=41) (actual time=0.003..0.655 rows=5,353 loops=1)

14. 0.643 40.435 ↑ 1.2 6,138 1

Subquery Scan on *SELECT* 2 (cost=9,275.91..19,773.11 rows=7,400 width=182) (actual time=11.576..40.435 rows=6,138 loops=1)

15. 9.021 39.792 ↑ 1.2 6,138 1

Hash Join (cost=9,275.91..18,293.11 rows=7,400 width=182) (actual time=11.575..39.792 rows=6,138 loops=1)

  • Hash Cond: (wages_age_1.revision_id = a.revision_id)
  • Join Filter: (c.employment_status && a.employment_status)
  • Rows Removed by Join Filter: 1,054
16. 2.258 16.005 ↓ 1.9 9,370 1

Merge Join (cost=8,976.30..11,213.33 rows=4,846 width=45) (actual time=11.318..16.005 rows=9,370 loops=1)

  • Merge Cond: (c.classification_id = wages_age_1.classification_id)
17. 1.493 1.493 ↓ 1.1 5,353 1

Index Scan using wages_classification_pkey on wages_classification c (cost=0.28..1,231.22 rows=5,064 width=41) (actual time=0.006..1.493 rows=5,353 loops=1)

18. 2.739 12.254 ↓ 1.9 9,370 1

Sort (cost=8,976.02..8,988.13 rows=4,846 width=8) (actual time=11.310..12.254 rows=9,370 loops=1)

  • Sort Key: wages_age_1.classification_id
  • Sort Method: quicksort Memory: 824kB
19. 7.376 9.515 ↓ 1.9 9,370 1

HashAggregate (cost=6,740.98..7,710.18 rows=4,846 width=8) (actual time=8.215..9.515 rows=9,370 loops=1)

  • Group Key: wages_age_1.classification_id, wages_age_1.revision_id
20. 2.139 2.139 ↓ 1.0 32,970 1

Seq Scan on wages_age wages_age_1 (cost=0.00..6,582.20 rows=31,756 width=8) (actual time=0.007..2.139 rows=32,970 loops=1)

21. 0.101 0.219 ↓ 1.1 769 1

Hash (cost=153.40..153.40 rows=722 width=57) (actual time=0.219..0.219 rows=769 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
22. 0.118 0.118 ↓ 1.1 769 1

Seq Scan on wages_allowance a (cost=0.00..153.40 rows=722 width=57) (actual time=0.006..0.118 rows=769 loops=1)

23.          

SubPlan (for Hash Join)

24. 4.849 14.547 ↑ 1.0 1 4,849

HashSetOp Intersect (cost=0.00..60.70 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=4,849)

25. 0.000 9.698 ↑ 50.0 4 4,849

Append (cost=0.00..60.20 rows=200 width=36) (actual time=0.001..0.002 rows=4 loops=4,849)

26. 0.000 4.849 ↑ 50.0 2 4,849

Subquery Scan on *SELECT* 1_1 (cost=0.00..30.10 rows=100 width=36) (actual time=0.000..0.001 rows=2 loops=4,849)

27. 4.849 4.849 ↑ 50.0 2 4,849

Result (cost=0.00..10.10 rows=100 width=32) (actual time=0.000..0.001 rows=2 loops=4,849)

28. 4.849 4.849 ↑ 100.0 1 4,849

Subquery Scan on *SELECT* 2_1 (cost=0.00..30.10 rows=100 width=36) (actual time=0.000..0.001 rows=1 loops=4,849)

29. 0.000 0.000 ↑ 100.0 1 4,849

Result (cost=0.00..10.10 rows=100 width=32) (actual time=0.000..0.000 rows=1 loops=4,849)

30. 0.451 18.583 ↑ 231.6 4,216 1

Subquery Scan on *SELECT* 3 (cost=9,145.12..305,363.53 rows=976,400 width=160) (actual time=11.256..18.583 rows=4,216 loops=1)

31. 2.590 18.132 ↑ 231.6 4,216 1

Hash Join (cost=9,145.12..110,083.53 rows=976,400 width=160) (actual time=11.256..18.132 rows=4,216 loops=1)

  • Hash Cond: (wages_age_2.revision_id = wages_penalty.revision_id)
32. 2.183 15.418 ↓ 1.9 9,370 1

Merge Join (cost=8,976.30..11,213.33 rows=4,846 width=45) (actual time=11.115..15.418 rows=9,370 loops=1)

  • Merge Cond: (wages_classification_1.classification_id = wages_age_2.classification_id)
33. 1.295 1.295 ↓ 1.1 5,353 1

Index Scan using wages_classification_pkey on wages_classification wages_classification_1 (cost=0.28..1,231.22 rows=5,064 width=41) (actual time=0.006..1.295 rows=5,353 loops=1)

34. 2.626 11.940 ↓ 1.9 9,370 1

Sort (cost=8,976.02..8,988.13 rows=4,846 width=8) (actual time=11.107..11.940 rows=9,370 loops=1)

  • Sort Key: wages_age_2.classification_id
  • Sort Method: quicksort Memory: 824kB
35. 7.323 9.314 ↓ 1.9 9,370 1

HashAggregate (cost=6,740.98..7,710.18 rows=4,846 width=8) (actual time=8.076..9.314 rows=9,370 loops=1)

  • Group Key: wages_age_2.classification_id, wages_age_2.revision_id
36. 1.991 1.991 ↓ 1.0 32,970 1

Seq Scan on wages_age wages_age_2 (cost=0.00..6,582.20 rows=31,756 width=8) (actual time=0.003..1.991 rows=32,970 loops=1)

37. 0.058 0.124 ↓ 1.1 439 1

Hash (cost=86.40..86.40 rows=407 width=28) (actual time=0.124..0.124 rows=439 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
38. 0.066 0.066 ↓ 1.1 439 1

Seq Scan on wages_penalty (cost=0.00..86.40 rows=407 width=28) (actual time=0.008..0.066 rows=439 loops=1)

39. 2.874 7.975 ↓ 1.1 11,733 1

Hash (cost=3,976.75..3,976.75 rows=10,265 width=46) (actual time=7.974..7.975 rows=11,733 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,060kB
40. 2.164 5.101 ↓ 1.1 11,733 1

Merge Join (cost=0.56..3,976.75 rows=10,265 width=46) (actual time=0.021..5.101 rows=11,733 loops=1)

  • Merge Cond: (wages_revision.agreement_id = condition.agreement_id)
41. 0.478 0.478 ↓ 1.0 1,650 1

Index Scan using wages_revision_410cd312 on wages_revision (cost=0.28..397.98 rows=1,594 width=20) (actual time=0.011..0.478 rows=1,650 loops=1)

42. 1.050 2.459 ↓ 1.8 11,966 1

Materialize (cost=0.28..1,571.96 rows=6,478 width=34) (actual time=0.009..2.459 rows=11,966 loops=1)

43. 1.409 1.409 ↓ 1.0 6,540 1

Index Scan using wages_condition_410cd312 on wages_condition condition (cost=0.28..1,555.77 rows=6,478 width=34) (actual time=0.004..1.409 rows=6,540 loops=1)

44. 4.314 55.376 ↑ 1.0 6,531 1

Hash (cost=4,434.46..4,434.46 rows=6,534 width=36) (actual time=55.376..55.376 rows=6,531 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 5,754kB
45. 0.669 51.062 ↑ 1.0 6,531 1

Subquery Scan on rules (cost=1,804.52..4,434.46 rows=6,534 width=36) (actual time=47.074..51.062 rows=6,531 loops=1)

46. 48.245 50.393 ↑ 1.0 6,531 1

HashAggregate (cost=1,804.52..3,127.66 rows=6,534 width=36) (actual time=47.072..50.393 rows=6,531 loops=1)

  • Group Key: wages_rule.condition_id
47. 2.148 2.148 ↓ 1.0 8,174 1

Seq Scan on wages_rule (cost=0.00..1,744.00 rows=8,070 width=242) (actual time=0.011..2.148 rows=8,174 loops=1)

48. 0.002 965.787 ↑ 1.0 1 1

Hash (cost=3,598,166.95..3,598,166.95 rows=1 width=28) (actual time=965.787..965.787 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.005 965.785 ↑ 1.0 1 1

Nested Loop Left Join (cost=466,409.17..3,598,166.95 rows=1 width=28) (actual time=965.783..965.785 rows=1 loops=1)

  • Join Filter: (avl.agreement_id = ovr.agreement_id)
50. 11.484 965.774 ↑ 1.0 1 1

Hash Right Join (cost=466,408.89..3,598,166.26 rows=1 width=32) (actual time=965.773..965.774 rows=1 loops=1)

  • Hash Cond: ((((unnest(wages_classification_2.employment_status)))::text = (wage.employment_status)::text) AND (wages_classification_jobs.job_id = roster.job_id) AND (wages_classification_2.agreement_id = COALESCE(ovr.agreement_id, wages_classification_3.agreement_id)))
  • Join Filter: ((wages_classification_2.level >= wages_classification_3.level) AND (wages_revision_1.valid_period @> annotation.day))
51. 50.423 82.281 ↑ 88.5 113,595 1

Merge Join (cost=13,541.74..1,021,328.03 rows=10,054,300 width=104) (actual time=17.365..82.281 rows=113,595 loops=1)

  • Merge Cond: (wages_classification_2.classification_id = wages_classification_jobs.classification_id)
52. 2.239 21.608 ↓ 1.9 9,370 1

Merge Join (cost=13,524.65..14,543.40 rows=4,846 width=65) (actual time=17.275..21.608 rows=9,370 loops=1)

  • Merge Cond: (wages_classification_2.classification_id = wages_age_3.classification_id)
53. 1.861 4.376 ↓ 1.1 5,353 1

Sort (cost=2,908.67..2,921.33 rows=5,064 width=49) (actual time=3.680..4.376 rows=5,353 loops=1)

  • Sort Key: wages_classification_2.classification_id
  • Sort Method: quicksort Memory: 757kB
54. 1.034 2.515 ↓ 1.1 5,353 1

Merge Join (cost=0.56..2,597.08 rows=5,064 width=49) (actual time=0.018..2.515 rows=5,353 loops=1)

  • Merge Cond: (wages_agreement.agreement_id = wages_classification_2.agreement_id)
55. 0.184 0.184 ↑ 1.2 1,082 1

Index Scan using wages_agreement_pkey on wages_agreement (cost=0.28..300.22 rows=1,287 width=8) (actual time=0.010..0.184 rows=1,082 loops=1)

56. 1.297 1.297 ↓ 1.1 5,353 1

Index Scan using wages_classification_410cd312 on wages_classification wages_classification_2 (cost=0.28..1,271.02 rows=5,064 width=49) (actual time=0.005..1.297 rows=5,353 loops=1)

57. 3.363 14.993 ↓ 1.9 9,370 1

Sort (cost=10,615.98..10,628.09 rows=4,846 width=16) (actual time=13.591..14.993 rows=9,370 loops=1)

  • Sort Key: wages_age_3.classification_id
  • Sort Method: quicksort Memory: 989kB
58. 1.932 11.630 ↓ 1.9 9,370 1

Hash Join (cost=7,393.57..10,319.34 rows=4,846 width=16) (actual time=8.490..11.630 rows=9,370 loops=1)

  • Hash Cond: (wages_age_3.revision_id = wages_revision_1.revision_id)
59. 7.308 9.333 ↓ 1.9 9,370 1

HashAggregate (cost=6,740.98..7,710.18 rows=4,846 width=8) (actual time=8.114..9.333 rows=9,370 loops=1)

  • Group Key: wages_age_3.classification_id, wages_age_3.revision_id
60. 2.025 2.025 ↓ 1.0 32,970 1

Seq Scan on wages_age wages_age_3 (cost=0.00..6,582.20 rows=31,756 width=8) (actual time=0.003..2.025 rows=32,970 loops=1)

61. 0.179 0.365 ↓ 1.0 1,650 1

Hash (cost=329.80..329.80 rows=1,594 width=16) (actual time=0.365..0.365 rows=1,650 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 97kB
62. 0.186 0.186 ↓ 1.0 1,650 1

Seq Scan on wages_revision wages_revision_1 (cost=0.00..329.80 rows=1,594 width=16) (actual time=0.009..0.186 rows=1,650 loops=1)

63. 10.250 10.250 ↓ 3.9 103,572 1

Index Only Scan using wages_classification_jobs_classification_id_job_id_key on wages_classification_jobs (cost=0.29..6,037.63 rows=26,391 width=8) (actual time=0.012..10.250 rows=103,572 loops=1)

  • Heap Fetches: 18,359
64. 0.005 872.009 ↑ 1.0 1 1

Hash (cost=452,866.94..452,866.94 rows=1 width=40) (actual time=872.009..872.009 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.003 872.004 ↑ 1.0 1 1

Nested Loop Left Join (cost=306,404.20..452,866.94 rows=1 width=40) (actual time=872.002..872.004 rows=1 loops=1)

  • Join Filter: (annotation.day <@ ovr.valid_period)
66. 18.362 871.993 ↑ 1.0 1 1

Merge Join (cost=306,404.06..452,858.39 rows=1 width=40) (actual time=871.991..871.993 rows=1 loops=1)

  • Merge Cond: (wage.pid = roster.pid)
  • Join Filter: (annotation.day <@ (daterange(wage.effective_from, lead(wage.effective_from, 1) OVER (?))))
67. 187.725 853.603 ↑ 1.2 298,609 1

WindowAgg (cost=306,371.91..381,370.81 rows=352,936 width=96) (actual time=641.864..853.603 rows=298,609 loops=1)

68. 99.118 665.878 ↑ 1.2 298,610 1

Sort (cost=306,371.91..307,254.25 rows=352,936 width=32) (actual time=641.849..665.878 rows=298,610 loops=1)

  • Sort Key: wage.pid, wage.effective_from
  • Sort Method: quicksort Memory: 34,449kB
69. 60.196 566.760 ↑ 1.0 342,999 1

Hash Left Join (cost=2,108.65..273,850.54 rows=352,936 width=32) (actual time=2.440..566.760 rows=342,999 loops=1)

  • Hash Cond: (wage.classification_id = wages_classification_3.classification_id)
70. 127.550 504.165 ↑ 1.0 342,999 1

Merge Join (cost=1.39..249,497.77 rows=352,936 width=24) (actual time=0.030..504.165 rows=342,999 loops=1)

  • Merge Cond: (wage.pid = people.id)
71. 177.505 177.505 ↑ 1.0 342,999 1

Index Scan using staff_wages_pid_key on staff_wages wage (cost=0.42..96,555.30 rows=352,936 width=20) (actual time=0.014..177.505 rows=342,999 loops=1)

72. 62.304 199.110 ↓ 1.5 345,945 1

Materialize (cost=0.42..87,566.78 rows=234,123 width=8) (actual time=0.010..199.110 rows=345,945 loops=1)

73. 136.806 136.806 ↓ 1.1 254,350 1

Index Scan using people_pkey on people (cost=0.42..86,981.47 rows=234,123 width=8) (actual time=0.009..136.806 rows=254,350 loops=1)

74. 1.160 2.399 ↓ 1.1 5,353 1

Hash (cost=1,081.80..1,081.80 rows=5,064 width=12) (actual time=2.399..2.399 rows=5,353 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 295kB
75. 1.239 1.239 ↓ 1.1 5,353 1

Seq Scan on wages_classification wages_classification_3 (cost=0.00..1,081.80 rows=5,064 width=12) (actual time=0.006..1.239 rows=5,353 loops=1)

76. 0.005 0.028 ↑ 1.0 1 1

Sort (cost=17.62..17.63 rows=1 width=20) (actual time=0.028..0.028 rows=1 loops=1)

  • Sort Key: roster.pid
  • Sort Method: quicksort Memory: 25kB
77. 0.001 0.023 ↑ 1.0 1 1

Nested Loop (cost=1.00..17.61 rows=1 width=20) (actual time=0.022..0.023 rows=1 loops=1)

78. 0.014 0.014 ↑ 1.0 1 1

Index Scan using roster_pkey on roster (cost=0.44..8.64 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (id = 29,685,006)
  • Filter: (NOT deleted)
79. 0.008 0.008 ↑ 1.0 1 1

Index Scan using roster_rosteredannotation_pkey on roster_rosteredannotation annotation (cost=0.56..8.77 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 29,685,006)
80. 0.008 0.008 ↓ 0.0 0 1

Index Scan using prevent_overlapping_overrides on wages_agreementoverride ovr (cost=0.14..8.35 rows=1 width=14) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (roster.loc_id = location_id)
81. 0.006 0.006 ↓ 0.0 0 1

Index Scan using person_availableclassification_c8b43de2 on person_availableclassification avl (cost=0.28..0.49 rows=1 width=12) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: (wage_id = wage.id)
Planning time : 3.331 ms
Execution time : 1,478.492 ms