explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XSgl

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 5,400.001 ↓ 0.0 0 1

HashAggregate (cost=632,306.91..632,306.94 rows=1 width=334) (actual time=5,400.001..5,400.001 rows=0 loops=1)

  • Group Key: "*SELECT* 1".code_meter, ((vista.year * 100) + vista.year_cycle), vista.id_deriv_cycle, vista.desc_manufacturer, vista.desc_model, vista.serial_number, CASE vista.multipoint WHEN 'S'::"char" THEN vista.pod ELSE vista.fpod END, now(), '58'::text, ('01'::text), vista.id_org_lvl, 'ERZ'::text, 'CMC'::text, now()
2. 0.556 5,399.999 ↓ 0.0 0 1

Hash Join (cost=632,306.33..632,306.88 rows=1 width=334) (actual time=5,399.999..5,399.999 rows=0 loops=1)

  • Hash Cond: (("*SELECT* 1".code_meter)::text = (vista.code_meter)::text)
3. 0.003 5,399.443 ↓ 0.0 0 1

HashAggregate (cost=632,240.23..632,240.44 rows=21 width=18) (actual time=5,399.443..5,399.443 rows=0 loops=1)

  • Group Key: "*SELECT* 1".code_meter, ('01'::text)
4. 201.192 5,399.440 ↓ 0.0 0 1

Append (cost=24,989.20..632,240.12 rows=21 width=18) (actual time=5,399.440..5,399.440 rows=0 loops=1)

5. 0.000 178.605 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=24,989.20..24,989.28 rows=4 width=18) (actual time=178.605..178.605 rows=0 loops=1)

6. 0.001 178.605 ↓ 0.0 0 1

HashAggregate (cost=24,989.20..24,989.24 rows=4 width=18) (actual time=178.605..178.605 rows=0 loops=1)

  • Group Key: cvpc.code_meter, '01'::text
7. 0.001 178.604 ↓ 0.0 0 1

Nested Loop (cost=24,903.60..24,989.18 rows=4 width=18) (actual time=178.604..178.604 rows=0 loops=1)

8. 76.510 178.603 ↓ 0.0 0 1

Bitmap Heap Scan on v_m_pci_principal_carga cvpc (cost=24,903.60..24,934.00 rows=4 width=18) (actual time=178.603..178.603 rows=0 loops=1)

  • Recheck Cond: ((grupo = 101655) AND (territorio = 'ERZ'::text))
  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 98
  • Heap Blocks: exact=41
9. 0.006 0.295 ↓ 0.0 0 1

BitmapAnd (cost=35.62..35.62 rows=8 width=0) (actual time=0.295..0.295 rows=0 loops=1)

10. 0.021 0.021 ↑ 1.0 98 1

Bitmap Index Scan on in_v_m_pci_principal_carga_grupo (cost=0.00..5.02 rows=98 width=0) (actual time=0.021..0.021 rows=98 loops=1)

  • Index Cond: (grupo = 101655)
11. 0.268 0.268 ↑ 1.0 1,342 1

Bitmap Index Scan on in_v_m_pci_principal_carga_territorio (cost=0.00..30.35 rows=1,342 width=0) (actual time=0.268..0.268 rows=1,342 loops=1)

  • Index Cond: (territorio = 'ERZ'::text)
12.          

SubPlan (forBitmap Heap Scan)

13. 101.798 101.798 ↑ 1.1 201,427 1

Seq Scan on cmc_supply (cost=0.00..24,315.58 rows=220,958 width=20) (actual time=0.006..101.798 rows=201,427 loops=1)

14. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..13.76 rows=4 width=18) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on meter mt (cost=0.00..0.00 rows=1 width=18) (never executed)

  • Filter: ((cvpc.code_meter)::text = (meter)::text)
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_sev on meter_sev mt_1 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc.code_meter)::text)
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_ges_erz_une on meter_ges_erz_une mt_2 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc.code_meter)::text)
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_fec on meter_fec mt_3 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc.code_meter)::text)
  • Heap Fetches: 0
19. 0.001 1.523 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=212.91..212.99 rows=4 width=18) (actual time=1.523..1.523 rows=0 loops=1)

20. 0.000 1.522 ↓ 0.0 0 1

HashAggregate (cost=212.91..212.95 rows=4 width=18) (actual time=1.522..1.522 rows=0 loops=1)

  • Group Key: cvpc_1.code_meter, '02'::text
21. 0.000 1.522 ↓ 0.0 0 1

Nested Loop (cost=36.60..212.89 rows=4 width=18) (actual time=1.522..1.522 rows=0 loops=1)

22. 0.002 1.522 ↓ 0.0 0 1

Nested Loop (cost=36.60..157.71 rows=4 width=18) (actual time=1.522..1.522 rows=0 loops=1)

  • Join Filter: ((cvpc_1.derivation)::text = (cs.code_derivation)::text)
23. 0.000 1.520 ↓ 0.0 0 1

Nested Loop (cost=36.18..155.86 rows=4 width=58) (actual time=1.520..1.520 rows=0 loops=1)

24. 0.113 1.520 ↓ 0.0 0 1

Bitmap Heap Scan on v_m_pci_principal_carga cvpc_1 (cost=35.62..133.50 rows=4 width=38) (actual time=1.520..1.520 rows=0 loops=1)

  • Recheck Cond: ((grupo = 101655) AND (territorio = 'ERZ'::text))
  • Filter: (NOT (SubPlan 4))
  • Rows Removed by Filter: 98
  • Heap Blocks: exact=41
25. 0.007 0.231 ↓ 0.0 0 1

BitmapAnd (cost=35.62..35.62 rows=8 width=0) (actual time=0.231..0.231 rows=0 loops=1)

26. 0.023 0.023 ↑ 1.0 98 1

Bitmap Index Scan on in_v_m_pci_principal_carga_grupo (cost=0.00..5.02 rows=98 width=0) (actual time=0.023..0.023 rows=98 loops=1)

  • Index Cond: (grupo = 101655)
27. 0.201 0.201 ↑ 1.0 1,342 1

Bitmap Index Scan on in_v_m_pci_principal_carga_territorio (cost=0.00..30.35 rows=1,342 width=0) (actual time=0.201..0.201 rows=1,342 loops=1)

  • Index Cond: (territorio = 'ERZ'::text)
28.          

SubPlan (forBitmap Heap Scan)

29. 0.000 1.176 ↑ 1.0 1 98

Limit (cost=0.42..8.44 rows=1 width=217) (actual time=0.012..0.012 rows=1 loops=98)

30. 1.176 1.176 ↑ 1.0 1 98

Index Scan using in_cmc_meter_code_meter on cmc_meter cm_3 (cost=0.42..8.44 rows=1 width=217) (actual time=0.012..0.012 rows=1 loops=98)

  • Index Cond: ((code_meter)::text = (cvpc_1.code_meter)::text)
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_supply_ord_derivation on cmc_supply_ord cso (cost=0.56..5.58 rows=1 width=20) (never executed)

  • Index Cond: (code_derivation = (cvpc_1.derivation)::text)
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Index Scan using uk_supply_derivation on cmc_supply cs (cost=0.42..0.45 rows=1 width=20) (never executed)

  • Index Cond: ((code_derivation)::text = (cso.code_derivation)::text)
  • Filter: ((code_state_supply)::text <> '11'::text)
33. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..13.76 rows=4 width=18) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on meter mt_4 (cost=0.00..0.00 rows=1 width=18) (never executed)

  • Filter: ((cvpc_1.code_meter)::text = (meter)::text)
35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_sev on meter_sev mt_5 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc_1.code_meter)::text)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_ges_erz_une on meter_ges_erz_une mt_6 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc_1.code_meter)::text)
  • Heap Fetches: 0
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using meter_meter_fec on meter_fec mt_7 (cost=0.56..4.59 rows=1 width=18) (never executed)

  • Index Cond: (meter = (cvpc_1.code_meter)::text)
  • Heap Fetches: 0
38. 0.000 5,018.120 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=581,388.75..581,388.91 rows=8 width=18) (actual time=5,018.120..5,018.120 rows=0 loops=1)

39. 0.001 5,018.120 ↓ 0.0 0 1

HashAggregate (cost=581,388.75..581,388.83 rows=8 width=18) (actual time=5,018.120..5,018.120 rows=0 loops=1)

  • Group Key: cvpc_2.code_meter, '03'::text
40. 0.002 5,018.119 ↓ 0.0 0 1

Nested Loop (cost=37.02..581,388.49 rows=52 width=18) (actual time=5,018.119..5,018.119 rows=0 loops=1)

  • Join Filter: ((cvpc_2.code_meter)::text = (pci.code_meter)::text)
41. 0.000 5,018.117 ↓ 0.0 0 1

Nested Loop (cost=36.46..581,350.76 rows=4 width=36) (actual time=5,018.117..5,018.117 rows=0 loops=1)

42. 0.385 5,018.117 ↓ 0.0 0 1

Nested Loop (cost=36.04..581,345.48 rows=4 width=44) (actual time=5,018.117..5,018.117 rows=0 loops=1)

43. 2.734 6.404 ↓ 12.2 98 1

Bitmap Heap Scan on v_m_pci_principal_carga cvpc_2 (cost=35.62..66.00 rows=8 width=18) (actual time=3.683..6.404 rows=98 loops=1)

  • Recheck Cond: ((grupo = 101655) AND (territorio = 'ERZ'::text))
  • Heap Blocks: exact=41
44. 0.006 3.670 ↓ 0.0 0 1

BitmapAnd (cost=35.62..35.62 rows=8 width=0) (actual time=3.670..3.670 rows=0 loops=1)

45. 0.013 0.013 ↑ 1.0 98 1

Bitmap Index Scan on in_v_m_pci_principal_carga_grupo (cost=0.00..5.02 rows=98 width=0) (actual time=0.013..0.013 rows=98 loops=1)

  • Index Cond: (grupo = 101655)
46. 3.651 3.651 ↑ 1.0 1,342 1

Bitmap Index Scan on in_v_m_pci_principal_carga_territorio (cost=0.00..30.35 rows=1,342 width=0) (actual time=3.651..3.651 rows=1,342 loops=1)

  • Index Cond: (territorio = 'ERZ'::text)
47. 2,830.534 5,011.328 ↓ 0.0 0 98

Index Scan using in_cmc_meter_code_meter on cmc_meter cm (cost=0.42..72,659.93 rows=1 width=26) (actual time=51.136..51.136 rows=0 loops=98)

  • Index Cond: ((code_meter)::text = (cvpc_2.code_meter)::text)
  • Filter: (NOT (SubPlan 3))
  • Rows Removed by Filter: 1
48.          

SubPlan (forIndex Scan)

49. 2,068.525 2,180.794 ↑ 9.6 318,841 98

Materialize (cost=0.00..137,628.97 rows=3,069,598 width=8) (actual time=0.000..22.253 rows=318,841 loops=98)

50. 112.269 112.269 ↑ 9.5 324,150 1

Seq Scan on cmc_integrator_meter (cost=0.00..110,289.98 rows=3,069,598 width=8) (actual time=0.004..112.269 rows=324,150 loops=1)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_supply on cmc_supply cs_1 (cost=0.42..1.31 rows=1 width=8) (never executed)

  • Index Cond: (id_supply = cm.id_supply)
  • Filter: ((code_state_supply)::text <> '11'::text)
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_pln_cmc_integrator on pln_cmc_integrator pci (cost=0.56..9.27 rows=13 width=18) (never executed)