explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7tm8

Settings
# exclusive inclusive rows x rows loops node
1. 0.249 636.808 ↑ 2.5 20 1

Sort (cost=3,073.76..3,073.89 rows=51 width=112) (actual time=636.771..636.808 rows=20 loops=1)

  • Sort Key: (COALESCE(wmain.str01, '0'::text))
  • Sort Method: quicksort Memory: 27kB
2.          

CTE wb_period

3. 7.222 7.222 ↓ 155.9 1,247 1

Seq Scan on batch (cost=0.00..146.00 rows=8 width=419) (actual time=0.048..7.222 rows=1,247 loops=1)

  • Filter: (((receive_time)::date >= COALESCE(to_date('01.01.2018'::text, 'DD.MM.YYYY'::text), '1900-01-01'::date)) AND ((receive_time)::date <= COALESCE(to_date('31.12.2018'::text, 'DD.MM.YYYY'::text), (now())::date)))
  • Rows Removed by Filter: 326
4.          

CTE w_baz_period

5. 43.009 83.756 ↓ 124.6 7,847 1

Hash Join (cost=0.26..528.28 rows=63 width=102) (actual time=18.648..83.756 rows=7,847 loops=1)

  • Hash Cond: (baz.batch_id = batch_1.batch_id)
6. 22.180 22.180 ↓ 1.0 10,287 1

Seq Scan on batch_az baz (cost=0.00..488.83 rows=10,283 width=102) (actual time=0.013..22.180 rows=10,287 loops=1)

7. 3.117 18.567 ↓ 155.9 1,247 1

Hash (cost=0.16..0.16 rows=8 width=8) (actual time=18.567..18.567 rows=1,247 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 65kB
8. 15.450 15.450 ↓ 155.9 1,247 1

CTE Scan on wb_period batch_1 (cost=0.00..0.16 rows=8 width=8) (actual time=0.057..15.450 rows=1,247 loops=1)

9.          

CTE w_baz_period_c

10. 33.825 50.565 ↓ 51.0 3,216 1

HashAggregate (cost=2.36..2.99 rows=63 width=140) (actual time=43.204..50.565 rows=3,216 loops=1)

  • Group Key: w_baz_period.zags_list_azkind_id, w_baz_period.az_number_num, w_baz_period.az_number_letter, w_baz_period.az_date, w_baz_period.gov_body_code, w_baz_period.government_list_body_id
11. 16.740 16.740 ↓ 124.6 7,847 1

CTE Scan on w_baz_period (cost=0.00..1.26 rows=63 width=140) (actual time=0.004..16.740 rows=7,847 loops=1)

12.          

CTE w_baz_st_period

13. 17.207 145.027 ↓ 0.0 0 1

Nested Loop (cost=0.00..3.14 rows=1 width=170) (actual time=145.027..145.027 rows=0 loops=1)

  • Join Filter: (waz.status_id = bazst.batch_az_status_id)
  • Rows Removed by Join Filter: 7847
14. 0.056 0.056 ↑ 1.0 1 1

Seq Scan on batch_az_status bazst (cost=0.00..1.09 rows=1 width=2) (actual time=0.049..0.056 rows=1 loops=1)

  • Filter: ((code)::text = 'complete'::text)
  • Rows Removed by Filter: 6
15. 127.764 127.764 ↓ 124.6 7,847 1

CTE Scan on w_baz_period waz (cost=0.00..1.26 rows=63 width=170) (actual time=18.657..127.764 rows=7,847 loops=1)

16.          

CTE w_baz_period_count

17. 8.435 74.007 ↓ 1.3 83 1

HashAggregate (cost=1.73..2.36 rows=63 width=24) (actual time=73.781..74.007 rows=83 loops=1)

  • Group Key: w_baz_period_c.government_list_body_id, w_baz_period_c.zags_list_azkind_id
18. 65.572 65.572 ↓ 51.0 3,216 1

CTE Scan on w_baz_period_c (cost=0.00..1.26 rows=63 width=24) (actual time=43.210..65.572 rows=3,216 loops=1)

19.          

CTE w_baz_st_period_count

20. 0.013 145.069 ↓ 0.0 0 1

HashAggregate (cost=0.03..0.04 rows=1 width=24) (actual time=145.069..145.069 rows=0 loops=1)

  • Group Key: w_baz_st_period.government_list_body_id, w_baz_st_period.zags_list_azkind_id
21. 145.056 145.056 ↓ 0.0 0 1

CTE Scan on w_baz_st_period (cost=0.00..0.02 rows=1 width=24) (actual time=145.056..145.056 rows=0 loops=1)

22.          

CTE w_baz_all_count

23. 9.290 96.283 ↑ 2.8 71 1

HashAggregate (cost=704.76..706.76 rows=200 width=16) (actual time=96.106..96.283 rows=71 loops=1)

  • Group Key: w_baz_all.government_list_body_id
24.          

CTE w_baz_all

25. 46.103 68.438 ↓ 3.8 3,949 1

HashAggregate (cost=668.78..679.06 rows=1,028 width=72) (actual time=59.241..68.438 rows=3,949 loops=1)

  • Group Key: baz_1.government_list_body_id, baz_1.zags_list_azkind_id, baz_1.az_number_num, baz_1.az_number_letter, baz_1.az_date, baz_1.gov_body_code
26. 22.335 22.335 ↓ 1.0 10,287 1

Seq Scan on batch_az baz_1 (cost=0.00..488.83 rows=10,283 width=72) (actual time=0.021..22.335 rows=10,287 loops=1)

27. 86.993 86.993 ↓ 3.8 3,949 1

CTE Scan on w_baz_all (cost=0.00..20.56 rows=1,028 width=16) (actual time=59.251..86.993 rows=3,949 loops=1)

28.          

CTE w_baz_st_all_count

29. 0.008 53.506 ↓ 0.0 0 1

HashAggregate (cost=668.39..670.39 rows=200 width=16) (actual time=53.506..53.506 rows=0 loops=1)

  • Group Key: w_baz_st_all.government_list_body_id
30.          

CTE w_baz_st_all

31. 0.016 53.492 ↓ 0.0 0 1

HashAggregate (cost=632.41..642.69 rows=1,028 width=72) (actual time=53.492..53.492 rows=0 loops=1)

  • Group Key: baz_2.government_list_body_id, baz_2.zags_list_azkind_id, baz_2.az_number_num, baz_2.az_number_letter, baz_2.az_date, baz_2.gov_body_code
32. 27.550 53.476 ↓ 0.0 0 1

Hash Join (cost=1.10..606.72 rows=1,468 width=72) (actual time=53.476..53.476 rows=0 loops=1)

  • Hash Cond: (baz_2.az_status_id = bazst_1.batch_az_status_id)
33. 25.880 25.880 ↓ 1.0 10,287 1

Seq Scan on batch_az baz_2 (cost=0.00..488.83 rows=10,283 width=74) (actual time=0.031..25.880 rows=10,287 loops=1)

34. 0.011 0.046 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=2) (actual time=0.046..0.046 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.035 0.035 ↑ 1.0 1 1

Seq Scan on batch_az_status bazst_1 (cost=0.00..1.09 rows=1 width=2) (actual time=0.029..0.035 rows=1 loops=1)

  • Filter: ((code)::text = 'complete'::text)
  • Rows Removed by Filter: 6
36. 53.498 53.498 ↓ 0.0 0 1

CTE Scan on w_baz_st_all (cost=0.00..20.56 rows=1,028 width=16) (actual time=53.498..53.498 rows=0 loops=1)

37.          

CTE w_sum

38. 0.335 74.682 ↑ 1.6 39 1

HashAggregate (cost=1.57..2.36 rows=63 width=40) (actual time=74.586..74.682 rows=39 loops=1)

  • Group Key: w_baz_period_count.government_list_body_id
39. 74.347 74.347 ↓ 1.3 83 1

CTE Scan on w_baz_period_count (cost=0.00..1.26 rows=63 width=16) (actual time=73.787..74.347 rows=83 loops=1)

40.          

CTE w_sum_st

41. 0.007 145.080 ↓ 0.0 0 1

HashAggregate (cost=0.02..0.04 rows=1 width=40) (actual time=145.080..145.080 rows=0 loops=1)

  • Group Key: w_baz_st_period_count.government_list_body_id
42. 145.073 145.073 ↓ 0.0 0 1

CTE Scan on w_baz_st_period_count (cost=0.00..0.02 rows=1 width=16) (actual time=145.073..145.073 rows=0 loops=1)

43.          

CTE wgov_id

44. 11.724 263.872 ↑ 1.3 38 1

Unique (cost=985.99..986.88 rows=51 width=220) (actual time=242.500..263.872 rows=38 loops=1)

45. 27.982 252.148 ↓ 75.4 3,844 1

Sort (cost=985.99..986.12 rows=51 width=220) (actual time=242.491..252.148 rows=3,844 loops=1)

  • Sort Key: baz_3.government_list_body_id, reg.region_configuration_id, reg.subject_code, reg.subject_name, gov.code, gov.name
  • Sort Method: quicksort Memory: 1190kB
46. 29.232 224.166 ↓ 75.4 3,844 1

Nested Loop (cost=146.67..984.55 rows=51 width=220) (actual time=15.486..224.166 rows=3,844 loops=1)

47. 41.403 175.714 ↓ 75.4 3,844 1

Nested Loop (cost=146.52..976.40 rows=51 width=180) (actual time=15.468..175.714 rows=3,844 loops=1)

48. 46.429 87.229 ↓ 153.9 7,847 1

Hash Join (cost=146.10..764.70 rows=51 width=8) (actual time=15.426..87.229 rows=7,847 loops=1)

  • Hash Cond: (baz_3.batch_id = wp.batch_id)
49. 25.464 25.464 ↓ 1.0 10,287 1

Seq Scan on batch_az baz_3 (cost=0.00..488.83 rows=10,283 width=16) (actual time=0.029..25.464 rows=10,287 loops=1)

50. 3.177 15.336 ↓ 155.9 1,247 1

Hash (cost=146.00..146.00 rows=8 width=8) (actual time=15.336..15.336 rows=1,247 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 65kB
51. 12.159 12.159 ↓ 155.9 1,247 1

Seq Scan on batch wp (cost=0.00..146.00 rows=8 width=8) (actual time=0.037..12.159 rows=1,247 loops=1)

  • Filter: (((receive_time)::date >= COALESCE(to_date('01.01.2018'::text, 'DD.MM.YYYY'::text), '1900-01-01'::date)) AND ((receive_time)::date <= COALESCE(to_date('31.12.2018'::text, 'DD.MM.YYYY'::text), (now())::d (...)
  • Rows Removed by Filter: 326
52. 47.082 47.082 ↓ 0.0 0 7,847

Index Scan using government_list_body_pkey on government_list_body gov (cost=0.42..4.15 rows=1 width=180) (actual time=0.006..0.006 rows=0 loops=7,847)

  • Index Cond: (government_list_body_id = baz_3.government_list_body_id)
53. 19.220 19.220 ↑ 1.0 1 3,844

Index Scan using region_configuration_pkey on region_configuration reg (cost=0.14..0.16 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=3,844)

  • Index Cond: (region_configuration_id = gov.region_configuration_id)
54.          

CTE w_main_select

55. 0.330 635.876 ↑ 2.5 20 1

HashAggregate (cost=16.69..17.71 rows=51 width=470) (actual time=635.816..635.876 rows=20 loops=1)

  • Group Key: wgov.region_configuration_id, wgov.subject_code, wgov.subject_name
56. 0.249 635.546 ↑ 1.3 38 1

Hash Left Join (cost=9.63..15.80 rows=51 width=422) (actual time=634.506..635.546 rows=38 loops=1)

  • Hash Cond: (wgov.government_list_body_id = summ.government_list_body_id)
57. 0.194 560.304 ↑ 1.3 38 1

Hash Left Join (cost=7.59..13.05 rows=51 width=398) (actual time=559.431..560.304 rows=38 loops=1)

  • Hash Cond: (wgov.government_list_body_id = summ_st.government_list_body_id)
58. 0.288 415.018 ↑ 1.3 38 1

Hash Right Join (cost=7.56..12.82 rows=51 width=366) (actual time=414.294..415.018 rows=38 loops=1)

  • Hash Cond: (all_.government_list_body_id = wgov.government_list_body_id)
59. 96.687 96.687 ↑ 2.8 71 1

CTE Scan on w_baz_all_count all_ (cost=0.00..4.00 rows=200 width=16) (actual time=96.197..96.687 rows=71 loops=1)

60. 0.141 318.043 ↑ 1.3 38 1

Hash (cost=6.92..6.92 rows=51 width=358) (actual time=318.043..318.043 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
61. 0.130 317.902 ↑ 1.3 38 1

Hash Right Join (cost=1.66..6.92 rows=51 width=358) (actual time=317.818..317.902 rows=38 loops=1)

  • Hash Cond: (all_st.government_list_body_id = wgov.government_list_body_id)
62. 53.512 53.512 ↓ 0.0 0 1

CTE Scan on w_baz_st_all_count all_st (cost=0.00..4.00 rows=200 width=16) (actual time=53.512..53.512 rows=0 loops=1)

63. 0.157 264.260 ↑ 1.3 38 1

Hash (cost=1.02..1.02 rows=51 width=350) (actual time=264.260..264.260 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
64. 264.103 264.103 ↑ 1.3 38 1

CTE Scan on wgov_id wgov (cost=0.00..1.02 rows=51 width=350) (actual time=242.509..264.103 rows=38 loops=1)

65. 0.006 145.092 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=145.092..145.092 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
66. 145.086 145.086 ↓ 0.0 0 1

CTE Scan on w_sum_st summ_st (cost=0.00..0.02 rows=1 width=40) (actual time=145.086..145.086 rows=0 loops=1)

67. 0.125 74.993 ↑ 1.7 38 1

Hash (cost=1.26..1.26 rows=63 width=40) (actual time=74.993..74.993 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
68. 74.868 74.868 ↑ 1.6 39 1

CTE Scan on w_sum summ (cost=0.00..1.26 rows=63 width=40) (actual time=74.595..74.868 rows=39 loops=1)

69.          

CTE w_main

70. 636.048 636.048 ↑ 2.5 20 1

CTE Scan on w_main_select wm (cost=0.00..2.81 rows=51 width=502) (actual time=635.856..636.048 rows=20 loops=1)

71.          

CTE wmain

72. 636.370 636.370 ↑ 2.5 20 1

CTE Scan on w_main (cost=0.00..1.53 rows=51 width=112) (actual time=636.064..636.370 rows=20 loops=1)

73. 636.559 636.559 ↑ 2.5 20 1

CTE Scan on wmain (cost=0.00..1.02 rows=51 width=112) (actual time=636.076..636.559 rows=20 loops=1)

Planning time : 6.458 ms
Execution time : 638.404 ms