explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Csv

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 45,034.688 ↓ 200.0 200 1

Limit (cost=135,450.65..135,450.65 rows=1 width=1,057) (actual time=45,034.662..45,034.688 rows=200 loops=1)

2. 169.895 45,034.680 ↓ 200.0 200 1

Sort (cost=135,450.65..135,450.65 rows=1 width=1,057) (actual time=45,034.662..45,034.680 rows=200 loops=1)

  • Sort Key: h3.id_habitante, h3.id_registro
  • Sort Method: top-N heapsort Memory: 228kB
3. 311.879 44,864.785 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.97..135,450.64 rows=1 width=1,057) (actual time=6,110.648..44,864.785 rows=75,537 loops=1)

  • Join Filter: (pth.cd_tipo_hoja = phh.cd_tipo_hoja)
  • Rows Removed by Join Filter: 151074
4. 1,890.479 44,552.906 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.97..135,449.57 rows=1 width=1,051) (actual time=6,110.640..44,552.906 rows=75,537 loops=1)

  • Join Filter: (((tpv.cd_prov_ent)::text = (phv.cd_prov_ent)::text) AND ((tpv.cd_mun_ent)::text = (phv.cd_mun_ent)::text) AND (tpv.cd_tipo_via = phv.cd_tipo_via))
  • Rows Removed by Join Filter: 21150360
5. 3,764.230 41,755.983 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.97..135,438.84 rows=1 width=1,057) (actual time=6,110.601..41,755.983 rows=75,537 loops=1)

  • Join Filter: (phv.id_registro = h3.pos_id_via_hist)
  • Rows Removed by Join Filter: 62771247
6. 414.193 35,347.958 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.97..135,406.12 rows=1 width=1,020) (actual time=6,110.574..35,347.958 rows=75,537 loops=1)

  • Join Filter: (phun.id_registro = h3.pos_id_unidadpob_hist)
  • Rows Removed by Join Filter: 1586277
7. 95.877 34,858.228 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.97..135,404.63 rows=1 width=808) (actual time=6,110.566..34,858.228 rows=75,537 loops=1)

8. 13,298.195 34,611.277 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.68..135,400.27 rows=1 width=756) (actual time=6,110.558..34,611.277 rows=75,537 loops=1)

  • Join Filter: (pht.id_registro = h3.pos_id_tramo_hist)
  • Rows Removed by Join Filter: 236808495
9. 248.838 11,266.661 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.68..135,271.71 rows=1 width=745) (actual time=6,110.487..11,266.661 rows=75,537 loops=1)

  • Join Filter: (psv.id_registro = h3.pos_id_pseudovia_hist)
10. 453.837 11,017.823 ↓ 75,537.0 75,537 1

Nested Loop Left Join (cost=104,117.68..135,254.51 rows=1 width=721) (actual time=6,110.482..11,017.823 rows=75,537 loops=1)

  • Join Filter: ((est.cd_estdo)::text = (h3.pos_cd_estdo)::text)
  • Rows Removed by Join Filter: 1359666
11. 65.866 10,488.449 ↓ 75,537.0 75,537 1

Nested Loop (cost=104,117.68..135,253.09 rows=1 width=709) (actual time=6,110.471..10,488.449 rows=75,537 loops=1)

12. 77.536 8,138.183 ↓ 142,775.0 142,775 1

GroupAggregate (cost=104,117.26..135,244.62 rows=1 width=19) (actual time=6,110.415..8,138.183 rows=142,775 loops=1)

13. 496.143 8,060.647 ↓ 144,748.0 144,748 1

Merge Join (cost=104,117.26..135,244.60 rows=1 width=19) (actual time=6,110.398..8,060.647 rows=144,748 loops=1)

  • Merge Cond: (((h.cd_prov_ent)::text = (h1.cd_prov_ent)::text) AND ((h.cd_mun_ent)::text = (h1.cd_mun_ent)::text) AND (h.id_habitante = h1.id_habitante))
  • Join Filter: (h1.fecha_real = (max(h.fecha_real)))
  • Rows Removed by Join Filter: 330324
14. 129.344 250.737 ↓ 1.5 142,775 1

GroupAggregate (cost=0.42..23,472.47 rows=97,945 width=19) (actual time=0.069..250.737 rows=142,775 loops=1)

15. 121.393 121.393 ↑ 1.0 474,572 1

Index Only Scan using phistorico_norden6 on phistorico h (cost=0.42..17,747.05 rows=474,597 width=19) (actual time=0.061..121.393 rows=474,572 loops=1)

  • Index Cond: (fecha_real <= '2018-12-31 00:00:00'::timestamp without time zone)
  • Heap Fetches: 0
16. 48.746 7,313.767 ↑ 1.0 475,272 1

Materialize (cost=104,116.84..106,493.28 rows=475,288 width=27) (actual time=6,110.291..7,313.767 rows=475,272 loops=1)

17. 6,817.251 7,265.021 ↑ 1.0 475,272 1

Sort (cost=104,116.84..105,305.06 rows=475,288 width=27) (actual time=6,110.288..7,265.021 rows=475,272 loops=1)

  • Sort Key: h1.cd_prov_ent, h1.cd_mun_ent, h1.id_habitante
  • Sort Method: external merge Disk: 19472kB
18. 447.770 447.770 ↑ 1.0 475,288 1

Seq Scan on phistorico h1 (cost=0.00..36,550.88 rows=475,288 width=27) (actual time=0.018..447.770 rows=475,288 loops=1)

19. 2,284.400 2,284.400 ↑ 1.0 1 142,775

Index Scan using phistorico_norden3 on phistorico h3 (cost=0.42..8.45 rows=1 width=709) (actual time=0.016..0.016 rows=1 loops=142,775)

  • Index Cond: (((cd_prov_ent)::text = '46'::text) AND ((cd_mun_ent)::text = '131'::text) AND (id_registro = (max(h1.id_registro))))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
20. 75.537 75.537 ↑ 1.0 19 75,537

Seq Scan on pestudio est (cost=0.00..1.19 rows=19 width=28) (actual time=0.000..0.001 rows=19 loops=75,537)

21. 0.000 0.000 ↓ 0.0 0 75,537

Seq Scan on phpseudovia psv (cost=0.00..13.20 rows=320 width=32) (actual time=0.000..0.000 rows=0 loops=75,537)

22. 10,046.421 10,046.421 ↑ 1.0 3,136 75,537

Seq Scan on phtramo pht (cost=0.00..89.36 rows=3,136 width=19) (actual time=0.001..0.133 rows=3,136 loops=75,537)

23. 151.074 151.074 ↑ 1.0 1 75,537

Index Scan using phhojapadronal_pkey on phhojapadronal phh (cost=0.29..4.34 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=75,537)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
24. 75.537 75.537 ↑ 1.0 22 75,537

Seq Scan on phunidadpob phun (cost=0.00..1.22 rows=22 width=220) (actual time=0.000..0.001 rows=22 loops=75,537)

25. 2,643.795 2,643.795 ↑ 1.0 832 75,537

Seq Scan on phvia phv (cost=0.00..22.32 rows=832 width=45) (actual time=0.001..0.035 rows=832 loops=75,537)

26. 906.444 906.444 ↑ 1.0 281 75,537

Seq Scan on ptipovia tpv (cost=0.00..5.81 rows=281 width=16) (actual time=0.001..0.012 rows=281 loops=75,537)

27. 0.000 0.000 ↑ 1.0 3 75,537

Seq Scan on ptipohoja pth (cost=0.00..1.03 rows=3 width=16) (actual time=0.000..0.000 rows=3 loops=75,537)