explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h3QB

Settings
# exclusive inclusive rows x rows loops node
1. 0.170 3,209.891 ↑ 2.6 20 1

Sort (cost=13,477,323.73..13,477,323.86 rows=52 width=112) (actual time=3,209.856..3,209.891 rows=20 loops=1)

  • Sort Key: (COALESCE((wm.str01)::text, '0'::text))
  • Sort Method: quicksort Memory: 27kB
2. 0.179 3,209.721 ↑ 2.6 20 1

Subquery Scan on wm (cost=13,476,756.75..13,477,322.25 rows=52 width=112) (actual time=3,209.306..3,209.721 rows=20 loops=1)

3. 0.199 3,209.542 ↑ 2.6 20 1

GroupAggregate (cost=13,476,756.75..13,477,319.39 rows=52 width=176) (actual time=3,209.265..3,209.542 rows=20 loops=1)

  • Group Key: reg.region_configuration_id, reg.subject_code, reg.subject_name
4. 0.382 3,209.343 ↑ 738.9 38 1

Sort (cost=13,476,756.75..13,476,826.95 rows=28,080 width=128) (actual time=3,209.244..3,209.343 rows=38 loops=1)

  • Sort Key: reg.region_configuration_id, reg.subject_code, reg.subject_name
  • Sort Method: quicksort Memory: 30kB
5. 0.526 3,208.961 ↑ 738.9 38 1

Nested Loop Left Join (cost=1,963.63..13,474,682.02 rows=28,080 width=128) (actual time=269.347..3,208.961 rows=38 loops=1)

6. 0.391 1,866.465 ↑ 738.9 38 1

Nested Loop Left Join (cost=1,728.82..6,880,048.33 rows=28,080 width=104) (actual time=236.702..1,866.465 rows=38 loops=1)

7. 0.589 537.062 ↑ 738.9 38 1

Nested Loop Left Join (cost=1,493.53..271,175.36 rows=28,080 width=72) (actual time=203.805..537.062 rows=38 loops=1)

8. 0.443 359.887 ↑ 12.3 38 1

Nested Loop Left Join (cost=977.10..27,359.85 rows=468 width=64) (actual time=200.533..359.887 rows=38 loops=1)

9. 8.766 213.334 ↑ 1.4 38 1

Unique (cost=460.50..461.41 rows=52 width=220) (actual time=196.738..213.334 rows=38 loops=1)

10. 22.374 204.568 ↓ 73.9 3,844 1

Sort (cost=460.50..460.63 rows=52 width=220) (actual time=196.732..204.568 rows=3,844 loops=1)

  • Sort Key: baz.government_list_body_id, reg.region_configuration_id, reg.subject_code, reg.subject_name, gov.code, gov.name
  • Sort Method: quicksort Memory: 1190kB
11. 25.150 182.194 ↓ 73.9 3,844 1

Nested Loop (cost=0.85..459.02 rows=52 width=220) (actual time=0.199..182.194 rows=3,844 loops=1)

12. 34.292 141.668 ↓ 73.9 3,844 1

Nested Loop (cost=0.71..450.72 rows=52 width=180) (actual time=0.185..141.668 rows=3,844 loops=1)

13. 34.420 68.141 ↓ 150.9 7,847 1

Nested Loop (cost=0.29..234.88 rows=52 width=8) (actual time=0.065..68.141 rows=7,847 loops=1)

14. 7.534 7.534 ↓ 155.9 1,247 1

Seq Scan on batch wp (cost=0.00..145.19 rows=8 width=8) (actual time=0.044..7.534 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'::tex (...)
  • Rows Removed by Filter: 326
15. 26.187 26.187 ↑ 1.3 6 1,247

Index Scan using pgpro__batch_az___batch_id on batch_az baz (cost=0.29..11.13 rows=8 width=16) (actual time=0.006..0.021 rows=6 loops=1,247)

  • Index Cond: (batch_id = wp.batch_id)
16. 39.235 39.235 ↓ 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.005..0.005 rows=0 loops=7,847)

  • Index Cond: (government_list_body_id = baz.government_list_body_id)
17. 15.376 15.376 ↑ 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.004..0.004 rows=1 loops=3,844)

  • Index Cond: (region_configuration_id = gov.region_configuration_id)
18. 0.190 146.110 ↓ 0.0 0 38

GroupAggregate (cost=516.59..517.09 rows=9 width=16) (actual time=3.845..3.845 rows=0 loops=38)

  • Group Key: baz_1.government_list_body_id
19. 0.418 145.920 ↓ 0.0 0 38

GroupAggregate (cost=516.59..516.86 rows=9 width=72) (actual time=3.840..3.840 rows=0 loops=38)

  • 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
20. 1.216 145.502 ↓ 0.0 0 38

Sort (cost=516.59..516.62 rows=9 width=72) (actual time=3.829..3.829 rows=0 loops=38)

  • Sort Key: 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
  • Sort Method: quicksort Memory: 25kB
21. 8.284 144.286 ↓ 0.0 0 38

Nested Loop (cost=0.00..516.45 rows=9 width=72) (actual time=3.797..3.797 rows=0 loops=38)

  • Join Filter: (baz_1.az_status_id = bazst.batch_az_status_id)
  • Rows Removed by Join Filter: 106
22. 0.608 0.608 ↑ 1.0 1 38

Seq Scan on batch_az_status bazst (cost=0.00..1.09 rows=1 width=2) (actual time=0.011..0.016 rows=1 loops=38)

  • Filter: ((code)::text = 'complete'::text)
  • Rows Removed by Filter: 6
23. 135.394 135.394 ↓ 1.7 106 38

Seq Scan on batch_az baz_1 (cost=0.00..514.59 rows=62 width=74) (actual time=0.845..3.563 rows=106 loops=38)

  • Filter: (government_list_body_id = baz.government_list_body_id)
  • Rows Removed by Filter: 10181
24. 4.446 176.586 ↑ 60.0 1 38

GroupAggregate (cost=516.43..519.77 rows=60 width=16) (actual time=4.645..4.647 rows=1 loops=38)

  • Group Key: baz_2.government_list_body_id
25. 14.934 172.140 ↑ 1.1 54 38

GroupAggregate (cost=516.43..518.27 rows=60 width=72) (actual time=3.953..4.530 rows=54 loops=38)

  • 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
26. 32.794 157.206 ↓ 1.7 106 38

Sort (cost=516.43..516.59 rows=62 width=72) (actual time=3.927..4.137 rows=106 loops=38)

  • Sort Key: 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
  • Sort Method: quicksort Memory: 25kB
27. 124.412 124.412 ↓ 1.7 106 38

Seq Scan on batch_az baz_2 (cost=0.00..514.59 rows=62 width=72) (actual time=0.813..3.274 rows=106 loops=38)

  • Filter: (government_list_body_id = baz.government_list_body_id)
  • Rows Removed by Filter: 10181
28. 0.266 1,329.012 ↓ 0.0 0 38

GroupAggregate (cost=235.29..235.34 rows=1 width=40) (actual time=34.974..34.974 rows=0 loops=38)

  • Group Key: baz_3.government_list_body_id
29. 0.304 1,328.746 ↓ 0.0 0 38

GroupAggregate (cost=235.29..235.31 rows=1 width=24) (actual time=34.967..34.967 rows=0 loops=38)

  • Group Key: baz_3.government_list_body_id, baz_3.zags_list_azkind_id
30. 0.608 1,328.442 ↓ 0.0 0 38

Sort (cost=235.29..235.29 rows=1 width=24) (actual time=34.959..34.959 rows=0 loops=38)

  • Sort Key: baz_3.zags_list_azkind_id
  • Sort Method: quicksort Memory: 25kB
31. 14.416 1,327.834 ↓ 0.0 0 38

Nested Loop (cost=145.73..235.28 rows=1 width=24) (actual time=34.943..34.943 rows=0 loops=38)

32. 221.084 1,298.042 ↓ 101.0 101 38

Nested Loop (cost=145.59..234.72 rows=1 width=26) (actual time=25.866..34.159 rows=101 loops=38)

33. 172.900 650.484 ↓ 155.9 1,247 38

Unique (cost=145.31..145.35 rows=8 width=8) (actual time=10.144..17.118 rows=1,247 loops=38)

34. 202.388 477.584 ↓ 155.9 1,247 38

Sort (cost=145.31..145.33 rows=8 width=8) (actual time=10.140..12.568 rows=1,247 loops=38)

  • Sort Key: batch.batch_id
  • Sort Method: quicksort Memory: 107kB
35. 275.196 275.196 ↓ 155.9 1,247 38

Seq Scan on batch (cost=0.00..145.19 rows=8 width=8) (actual time=0.028..7.242 rows=1,247 loops=38)

  • 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'::tex (...)
  • Rows Removed by Filter: 326
36. 426.474 426.474 ↓ 0.0 0 47,386

Index Scan using pgpro__batch_az___batch_id on batch_az baz_3 (cost=0.29..11.15 rows=1 width=34) (actual time=0.009..0.009 rows=0 loops=47,386)

  • Index Cond: (batch_id = batch.batch_id)
  • Filter: (government_list_body_id = baz.government_list_body_id)
  • Rows Removed by Filter: 6
37. 15.376 15.376 ↓ 0.0 0 3,844

Index Scan using pk_batch_az_status on batch_az_status bazst_1 (cost=0.13..0.35 rows=1 width=2) (actual time=0.004..0.004 rows=0 loops=3,844)

  • Index Cond: (batch_az_status_id = baz_3.az_status_id)
  • Filter: ((code)::text = 'complete'::text)
  • Rows Removed by Filter: 0
38. 0.912 1,341.970 ↑ 1.0 1 38

GroupAggregate (cost=234.81..234.83 rows=1 width=40) (actual time=35.313..35.315 rows=1 loops=38)

  • Group Key: w_baz_period_count.government_list_body_id
39. 1.216 1,341.058 ↓ 2.0 2 38

Sort (cost=234.81..234.81 rows=1 width=16) (actual time=35.287..35.291 rows=2 loops=38)

  • Sort Key: w_baz_period_count.government_list_body_id
  • Sort Method: quicksort Memory: 25kB
40. 0.494 1,339.842 ↓ 2.0 2 38

Subquery Scan on w_baz_period_count (cost=234.73..234.80 rows=1 width=16) (actual time=34.784..35.259 rows=2 loops=38)

41. 4.294 1,339.348 ↓ 2.0 2 38

GroupAggregate (cost=234.73..234.79 rows=1 width=24) (actual time=34.778..35.246 rows=2 loops=38)

  • Group Key: baz_4.government_list_body_id, baz_4.zags_list_azkind_id
42. 13.642 1,335.054 ↓ 49.0 49 38

GroupAggregate (cost=234.73..234.76 rows=1 width=72) (actual time=34.603..35.133 rows=49 loops=38)

  • Group Key: baz_4.zags_list_azkind_id, baz_4.az_number_num, baz_4.az_number_letter, baz_4.az_date, baz_4.gov_body_code, baz_4.government_list_body_id
43. 31.084 1,321.412 ↓ 101.0 101 38

Sort (cost=234.73..234.73 rows=1 width=72) (actual time=34.579..34.774 rows=101 loops=38)

  • Sort Key: baz_4.zags_list_azkind_id, baz_4.az_number_num, baz_4.az_number_letter, baz_4.az_date, baz_4.gov_body_code
  • Sort Method: quicksort Memory: 25kB
44. 207.252 1,290.328 ↓ 101.0 101 38

Nested Loop (cost=145.59..234.72 rows=1 width=72) (actual time=25.817..33.956 rows=101 loops=38)

45. 171.038 656.602 ↓ 155.9 1,247 38

Unique (cost=145.31..145.35 rows=8 width=8) (actual time=10.381..17.279 rows=1,247 loops=38)

46. 203.224 485.564 ↓ 155.9 1,247 38

Sort (cost=145.31..145.33 rows=8 width=8) (actual time=10.377..12.778 rows=1,247 loops=38)

  • Sort Key: batch_1.batch_id
  • Sort Method: quicksort Memory: 107kB
47. 282.340 282.340 ↓ 155.9 1,247 38

Seq Scan on batch batch_1 (cost=0.00..145.19 rows=8 width=8) (actual time=0.024..7.430 rows=1,247 loops=38)

  • 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 (...)
  • Rows Removed by Filter: 326
48. 426.474 426.474 ↓ 0.0 0 47,386

Index Scan using pgpro__batch_az___batch_id on batch_az baz_4 (cost=0.29..11.15 rows=1 width=80) (actual time=0.009..0.009 rows=0 loops=47,386)

  • Index Cond: (batch_id = batch_1.batch_id)
  • Filter: (government_list_body_id = baz.government_list_body_id)
  • Rows Removed by Filter: 6
Planning time : 5.698 ms
Execution time : 3,211.041 ms