explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uVQh : all part part 2

Settings
# exclusive inclusive rows x rows loops node
1. 25.804 8,637.904 ↓ 29,436.0 29,436 1

Nested Loop Left Join (cost=1.69..88,643.11 rows=1 width=218) (actual time=68.000..8,637.904 rows=29,436 loops=1)

2. 11.066 7,758.456 ↓ 29,436.0 29,436 1

Nested Loop (cost=0.84..88,618.31 rows=1 width=214) (actual time=67.916..7,758.456 rows=29,436 loops=1)

  • Join Filter: ((m_point_int_status.m_point)::text = (contract.m_point)::text)
3. 17.529 6,481.169 ↓ 29,447.0 29,447 1

Nested Loop (cost=0.84..88,602.62 rows=1 width=273) (actual time=67.838..6,481.169 rows=29,447 loops=1)

4. 18.744 5,520.792 ↓ 29,464.0 29,464 1

Nested Loop Left Join (cost=0.84..88,594.21 rows=1 width=256) (actual time=67.778..5,520.792 rows=29,464 loops=1)

5. 11.159 4,601.313 ↓ 16,995.0 16,995 1

Nested Loop Left Join (cost=0.56..88,529.68 rows=1 width=248) (actual time=67.709..4,601.313 rows=16,995 loops=1)

6. 1.779 3,638.434 ↓ 16,995.0 16,995 1

Nested Loop (cost=0.28..88,495.37 rows=1 width=240) (actual time=67.637..3,638.434 rows=16,995 loops=1)

7. 8.873 3,262.765 ↓ 4,248.8 16,995 1

Nested Loop (cost=0.28..88,460.93 rows=4 width=240) (actual time=67.590..3,262.765 rows=16,995 loops=1)

  • Join Filter: ((m_point_int_status.m_point)::text = (m_point_read_mode.m_point)::text)
8. 11.678 2,523.107 ↓ 16,995.0 16,995 1

Nested Loop (cost=0.28..88,452.59 rows=1 width=194) (actual time=67.506..2,523.107 rows=16,995 loops=1)

  • Join Filter: ((measurement_point.m_point)::text = (m_point_int_status.m_point)::text)
9. 6.651 1,793.806 ↓ 17,503.0 17,503 1

Nested Loop Left Join (cost=0.28..88,444.45 rows=1 width=155) (actual time=67.259..1,793.806 rows=17,503 loops=1)

10. 7.278 771.981 ↓ 17,503.0 17,503 1

Nested Loop (cost=0.00..88,435.57 rows=1 width=147) (actual time=67.141..771.981 rows=17,503 loops=1)

11. 1.862 183.258 ↓ 985.5 9,855 1

Append (cost=0.00..88,309.05 rows=10 width=93) (actual time=67.058..183.258 rows=9,855 loops=1)

12. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on measurement_point (cost=0.00..0.00 rows=1 width=93) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (((pod)::text >= 'ES0031300000521054KK0F'::text) AND ((pod)::text < 'ES0031300001901044ZB0F'::text) AND ((status)::text = 'ACTIVE'::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
13. 117.010 181.396 ↓ 1,095.0 9,855 1

Bitmap Heap Scan on measurement_point_ges_erz_une (cost=1,226.84..88,309.05 rows=9 width=93) (actual time=67.057..181.396 rows=9,855 loops=1)

  • Recheck Cond: (((pod)::text >= 'ES0031300000521054KK0F'::text) AND ((pod)::text < 'ES0031300001901044ZB0F'::text))
  • Filter: (((status)::text = 'ACTIVE'::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 31121
  • Heap Blocks: exact=13274
14. 64.386 64.386 ↓ 1.5 41,013 1

Bitmap Index Scan on m_point_pod_ges_erz_une (cost=0.00..1,226.84 rows=28,228 width=0) (actual time=64.386..64.386 rows=41,013 loops=1)

  • Index Cond: (((pod)::text >= 'ES0031300000521054KK0F'::text) AND ((pod)::text < 'ES0031300001901044ZB0F'::text))
15. 19.710 581.445 ↑ 1.0 2 9,855

Append (cost=0.00..12.63 rows=2 width=54) (actual time=0.054..0.059 rows=2 loops=9,855)

16. 0.000 0.000 ↓ 0.0 0 9,855

Seq Scan on m_point_meter (cost=0.00..0.00 rows=1 width=54) (actual time=0.000..0.000 rows=0 loops=9,855)

  • Filter: (((measurement_point.m_point)::text = (m_point)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
17. 561.735 561.735 ↓ 2.0 2 9,855

Index Scan using mp_meter_m_point_end_reference_ges_erz_une on m_point_meter_ges_erz_une (cost=0.57..12.63 rows=1 width=54) (actual time=0.052..0.057 rows=2 loops=9,855)

  • Index Cond: (((m_point)::text = (measurement_point.m_point)::text) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference))
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 0
18. 35.006 1,015.174 ↑ 1.0 1 17,503

Nested Loop (cost=0.28..8.86 rows=1 width=44) (actual time=0.057..0.058 rows=1 loops=17,503)

19. 0.000 910.156 ↑ 1.0 1 17,503

Nested Loop (cost=0.00..7.55 rows=1 width=38) (actual time=0.051..0.052 rows=1 loops=17,503)

20. 17.503 647.611 ↑ 2.0 1 17,503

Append (cost=0.00..4.58 rows=2 width=50) (actual time=0.036..0.037 rows=1 loops=17,503)

21. 0.000 0.000 ↓ 0.0 0 17,503

Seq Scan on m_point_address mpa (cost=0.00..0.00 rows=1 width=50) (actual time=0.000..0.000 rows=0 loops=17,503)

  • Filter: (((measurement_point.m_point)::text = (m_point)::text) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
22. 630.108 630.108 ↑ 1.0 1 17,503

Index Only Scan using pk_m_point_address_ges_erz_une on m_point_address_ges_erz_une mpa_1 (cost=0.56..4.58 rows=1 width=50) (actual time=0.035..0.036 rows=1 loops=17,503)

  • Index Cond: (m_point = (measurement_point.m_point)::text)
  • Filter: (substr((m_point)::text, 1, 3) = 'ERZ'::text)
  • Heap Fetches: 0
23. 17.503 262.545 ↑ 2.0 1 17,503

Append (cost=0.00..1.46 rows=2 width=15) (actual time=0.015..0.015 rows=1 loops=17,503)

24. 0.000 0.000 ↓ 0.0 0 17,503

Seq Scan on country_division cd (cost=0.00..0.00 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=17,503)

  • Filter: ((zip_code IS NOT NULL) AND ((mpa.country_division)::text = (country_division)::text) AND (substr((country_division)::text, 1, 3) = 'ERZ'::text))
25. 245.042 245.042 ↑ 1.0 1 17,503

Index Only Scan using country_division_view_ges_erz_une on country_division_ges_erz_une cd_1 (cost=0.42..1.46 rows=1 width=15) (actual time=0.014..0.014 rows=1 loops=17,503)

  • Index Cond: ((country_division = (mpa.country_division)::text) AND (zip_code IS NOT NULL))
  • Filter: (substr((country_division)::text, 1, 3) = 'ERZ'::text)
  • Heap Fetches: 16269
26. 70.012 70.012 ↑ 1.0 1 17,503

Index Only Scan using zip_code_territory_pkey on zip_code_territory tv (cost=0.28..1.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=17,503)

  • Index Cond: (zip_code = (cd.zip_code)::text)
  • Heap Fetches: 0
27. 35.006 717.623 ↑ 2.0 1 17,503

Append (cost=0.00..8.12 rows=2 width=39) (actual time=0.040..0.041 rows=1 loops=17,503)

28. 0.000 0.000 ↓ 0.0 0 17,503

Seq Scan on m_point_int_status (cost=0.00..0.00 rows=1 width=39) (actual time=0.000..0.000 rows=0 loops=17,503)

  • Filter: (((m_point_meter.m_point)::text = (m_point)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
29. 682.617 682.617 ↑ 1.0 1 17,503

Index Scan using pk_m_point_int_status_ges_erz_une on m_point_int_status_ges_erz_une (cost=0.56..8.12 rows=1 width=39) (actual time=0.039..0.039 rows=1 loops=17,503)

  • Index Cond: (((m_point)::text = (m_point_meter.m_point)::text) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference))
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 0
30. 33.990 730.785 ↑ 2.0 1 16,995

Append (cost=0.00..8.31 rows=2 width=46) (actual time=0.042..0.043 rows=1 loops=16,995)

31. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on m_point_read_mode (cost=0.00..0.00 rows=1 width=46) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((m_point_meter.m_point)::text = (m_point)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
32. 696.795 696.795 ↑ 1.0 1 16,995

Index Scan using pk_m_point_read_mode_ges_erz_une on m_point_read_mode_ges_erz_une (cost=0.56..8.31 rows=1 width=46) (actual time=0.041..0.041 rows=1 loops=16,995)

  • Index Cond: (((m_point)::text = (m_point_meter.m_point)::text) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference))
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 0
33. 33.990 373.890 ↑ 2.0 1 16,995

Append (cost=0.00..8.59 rows=2 width=20) (actual time=0.021..0.022 rows=1 loops=16,995)

34. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on derivation (cost=0.00..0.00 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((measurement_point.derivation)::text = (derivation)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((derivation)::text, 1, 3) = 'ERZ'::text))
35. 339.900 339.900 ↑ 1.0 1 16,995

Index Scan using derivation_derivation_ges_erz_une on derivation_ges_erz_une (cost=0.56..8.59 rows=1 width=20) (actual time=0.020..0.020 rows=1 loops=16,995)

  • Index Cond: ((derivation)::text = (measurement_point.derivation)::text)
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((derivation)::text, 1, 3) = 'ERZ'::text))
36. 7.080 951.720 ↓ 0.0 0 16,995

Nested Loop (cost=0.28..34.30 rows=1 width=44) (actual time=0.056..0.056 rows=0 loops=16,995)

37. 5.655 934.725 ↓ 0.0 0 16,995

Nested Loop (cost=0.00..33.04 rows=1 width=37) (actual time=0.054..0.055 rows=0 loops=16,995)

38. 16.995 849.750 ↓ 0.0 0 16,995

Nested Loop (cost=0.00..9.82 rows=1 width=47) (actual time=0.050..0.050 rows=0 loops=16,995)

39. 16.995 594.825 ↑ 2.0 1 16,995

Append (cost=0.00..4.58 rows=2 width=50) (actual time=0.035..0.035 rows=1 loops=16,995)

40. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on m_point_address mpa_4 (cost=0.00..0.00 rows=1 width=50) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((measurement_point.m_point)::text = (m_point)::text) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
41. 577.830 577.830 ↑ 1.0 1 16,995

Index Only Scan using pk_m_point_address_ges_erz_une on m_point_address_ges_erz_une mpa_5 (cost=0.56..4.58 rows=1 width=50) (actual time=0.034..0.034 rows=1 loops=16,995)

  • Index Cond: (m_point = (measurement_point.m_point)::text)
  • Filter: (substr((m_point)::text, 1, 3) = 'ERZ'::text)
  • Heap Fetches: 0
42. 16.995 237.930 ↓ 0.0 0 16,995

Append (cost=0.00..2.60 rows=2 width=24) (actual time=0.014..0.014 rows=0 loops=16,995)

43. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on country_division cd_4 (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((zip_code IS NULL) OR ((zip_code)::text = ''::text)) AND ((mpa_4.country_division)::text = (country_division)::text) AND (substr((country_division)::text, 1, 3) = 'ERZ'::text))
44. 220.935 220.935 ↓ 0.0 0 16,995

Index Scan using country_division_view_ges_erz_une on country_division_ges_erz_une cd_5 (cost=0.42..2.60 rows=1 width=24) (actual time=0.013..0.013 rows=0 loops=16,995)

  • Index Cond: ((country_division)::text = (mpa_4.country_division)::text)
  • Filter: (((zip_code IS NULL) OR ((zip_code)::text = ''::text)) AND (substr((country_division)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 1
45. 6.610 79.320 ↑ 4.0 1 3,305

Append (cost=0.00..23.19 rows=4 width=15) (actual time=0.019..0.024 rows=1 loops=3,305)

46. 0.000 0.000 ↓ 0.0 0 3,305

Seq Scan on country_division parent (cost=0.00..0.00 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=3,305)

  • Filter: (((country_division_type)::text = '2'::text) AND ((cd_4.parent_country_division)::text = (country_division)::text))
47. 16.525 16.525 ↓ 0.0 0 3,305

Index Scan using country_division_view_sev on country_division_sev parent_1 (cost=0.42..7.84 rows=1 width=15) (actual time=0.005..0.005 rows=0 loops=3,305)

  • Index Cond: ((country_division)::text = (cd_4.parent_country_division)::text)
  • Filter: ((country_division_type)::text = '2'::text)
48. 42.965 42.965 ↑ 1.0 1 3,305

Index Scan using country_division_view_ges_erz_une on country_division_ges_erz_une parent_2 (cost=0.42..7.60 rows=1 width=15) (actual time=0.013..0.013 rows=1 loops=3,305)

  • Index Cond: ((country_division)::text = (cd_4.parent_country_division)::text)
  • Filter: ((country_division_type)::text = '2'::text)
49. 13.220 13.220 ↓ 0.0 0 3,305

Index Scan using country_division_view_fec on country_division_fec parent_3 (cost=0.42..7.74 rows=1 width=15) (actual time=0.004..0.004 rows=0 loops=3,305)

  • Index Cond: ((country_division)::text = (cd_4.parent_country_division)::text)
  • Filter: ((country_division_type)::text = '2'::text)
50. 9.915 9.915 ↓ 0.0 0 3,305

Index Only Scan using zip_code_territory_pkey on zip_code_territory tv_2 (cost=0.28..1.25 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=3,305)

  • Index Cond: (zip_code = (parent.zip_code)::text)
  • Heap Fetches: 0
51. 15.715 900.735 ↑ 1.0 1 16,995

Nested Loop (cost=0.28..64.52 rows=1 width=44) (actual time=0.048..0.053 rows=1 loops=16,995)

52. 16.995 798.765 ↑ 1.0 1 16,995

Nested Loop (cost=0.00..63.22 rows=1 width=38) (actual time=0.046..0.047 rows=1 loops=16,995)

53. 16.995 577.830 ↑ 2.0 1 16,995

Append (cost=0.00..4.58 rows=2 width=50) (actual time=0.034..0.034 rows=1 loops=16,995)

54. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on m_point_address mpa_2 (cost=0.00..0.00 rows=1 width=50) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((measurement_point.m_point)::text = (m_point)::text) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text))
55. 560.835 560.835 ↑ 1.0 1 16,995

Index Only Scan using pk_m_point_address_ges_erz_une on m_point_address_ges_erz_une mpa_3 (cost=0.56..4.58 rows=1 width=50) (actual time=0.033..0.033 rows=1 loops=16,995)

  • Index Cond: (m_point = (measurement_point.m_point)::text)
  • Filter: (substr((m_point)::text, 1, 3) = 'ERZ'::text)
  • Heap Fetches: 0
56. 0.000 203.940 ↑ 2.0 1 16,995

Append (cost=0.00..29.30 rows=2 width=13) (actual time=0.011..0.012 rows=1 loops=16,995)

57. 0.000 0.000 ↓ 0.0 0 16,995

Seq Scan on country_division cd_2 (cost=0.00..0.00 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=16,995)

  • Filter: (((mpa_2.country_division)::text = (parent_country_division)::text) AND (substr((country_division)::text, 1, 3) = 'ERZ'::text))
58. 203.940 203.940 ↑ 1.0 1 16,995

Index Scan using country_division_parent_ges_erz_une on country_division_ges_erz_une cd_3 (cost=0.42..29.30 rows=1 width=13) (actual time=0.011..0.012 rows=1 loops=16,995)

  • Index Cond: ((parent_country_division)::text = (mpa_2.country_division)::text)
  • Filter: (substr((country_division)::text, 1, 3) = 'ERZ'::text)
59. 86.255 86.255 ↑ 1.0 1 17,251

Index Only Scan using zip_code_territory_pkey on zip_code_territory tv_1 (cost=0.28..1.28 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=17,251)

  • Index Cond: (zip_code = (cd_2.zip_code)::text)
  • Heap Fetches: 0
60. 29.464 942.848 ↑ 2.0 1 29,464

Append (cost=0.00..8.39 rows=2 width=35) (actual time=0.032..0.032 rows=1 loops=29,464)

61. 0.000 0.000 ↓ 0.0 0 29,464

Seq Scan on meter (cost=0.00..0.00 rows=1 width=35) (actual time=0.000..0.000 rows=0 loops=29,464)

  • Filter: (((m_point_meter.meter)::text = (meter)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((meter)::text, 1, 3) = 'ERZ'::text))
62. 913.384 913.384 ↑ 1.0 1 29,464

Index Scan using meter_meter_ges_erz_une on meter_ges_erz_une (cost=0.56..8.39 rows=1 width=35) (actual time=0.031..0.031 rows=1 loops=29,464)

  • Index Cond: ((meter)::text = (m_point_meter.meter)::text)
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((meter)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 0
63. 29.447 1,266.221 ↑ 2.0 1 29,447

Append (cost=0.00..15.67 rows=2 width=85) (actual time=0.042..0.043 rows=1 loops=29,447)

64. 0.000 0.000 ↓ 0.0 0 29,447

Seq Scan on contract (cost=0.00..0.00 rows=1 width=85) (actual time=0.000..0.000 rows=0 loops=29,447)

  • Filter: (((m_point_meter.m_point)::text = (m_point)::text) AND (start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text) AND (substr((contract)::text, 1, 3) = 'ERZ'::text))
65. 1,236.774 1,236.774 ↑ 1.0 1 29,447

Index Scan using contract_m_point_ges_erz_une on contract_ges_erz_une (cost=0.56..15.67 rows=1 width=85) (actual time=0.041..0.042 rows=1 loops=29,447)

  • Index Cond: ((m_point)::text = (m_point_meter.m_point)::text)
  • Filter: ((start_reference <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)) AND (to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text) <= end_reference) AND (substr((m_point)::text, 1, 3) = 'ERZ'::text) AND (substr((contract)::text, 1, 3) = 'ERZ'::text))
  • Rows Removed by Filter: 1
66. 58.908 853.644 ↑ 3.0 1 29,436

Nested Loop (cost=0.84..24.77 rows=3 width=24) (actual time=0.024..0.029 rows=1 loops=29,436)

67. 677.028 677.028 ↑ 1.5 2 29,436

Index Scan using in_deriv_group_derivation on pln_deriv_group (cost=0.56..18.37 rows=3 width=28) (actual time=0.020..0.023 rows=2 loops=29,436)

  • Index Cond: ((derivation.derivation)::text = (derivation)::text)
  • Filter: ((end_date IS NULL) AND (start_date <= to_timestamp('2019-05-29'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 0
68. 117.708 117.708 ↑ 1.0 1 58,854

Index Scan using pk_group on pln_group (cost=0.28..2.12 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=58,854)

  • Index Cond: (id_group = pln_deriv_group.id_group)
  • Filter: ((d_planning_type)::text = '1'::text)
  • Rows Removed by Filter: 0
Planning time : 112.764 ms
Execution time : 8,641.422 ms