explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aPeGU

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 6,270.483 ↓ 200.0 200 1

Limit (cost=104,117.97..135,450.51 rows=1 width=1,049) (actual time=6,162.240..6,270.483 rows=200 loops=1)

2. 0.838 6,270.461 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,450.51 rows=1 width=1,049) (actual time=6,162.239..6,270.461 rows=200 loops=1)

  • Join Filter: (pth.cd_tipo_hoja = phh.cd_tipo_hoja)
  • Rows Removed by Join Filter: 398
3. 4.978 6,269.623 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,449.45 rows=1 width=1,043) (actual time=6,162.232..6,269.623 rows=200 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: 55979
4. 9.724 6,262.245 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,438.72 rows=1 width=1,049) (actual time=6,162.193..6,262.245 rows=200 loops=1)

  • Join Filter: (phv.id_registro = h3.pos_id_via_hist)
  • Rows Removed by Join Filter: 165566
5. 1.085 6,245.321 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,406.00 rows=1 width=1,012) (actual time=6,162.168..6,245.321 rows=200 loops=1)

  • Join Filter: (phun.id_registro = h3.pos_id_unidadpob_hist)
  • Rows Removed by Join Filter: 4180
6. 0.072 6,244.036 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,404.50 rows=1 width=808) (actual time=6,162.160..6,244.036 rows=200 loops=1)

7. 35.621 6,243.364 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,400.15 rows=1 width=756) (actual time=6,162.153..6,243.364 rows=200 loops=1)

  • Join Filter: (pht.id_registro = h3.pos_id_tramo_hist)
  • Rows Removed by Join Filter: 624557
8. 0.670 6,181.343 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,271.59 rows=1 width=745) (actual time=6,162.082..6,181.343 rows=200 loops=1)

  • Join Filter: (psv.id_registro = h3.pos_id_pseudovia_hist)
9. 1.494 6,180.673 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,254.39 rows=1 width=721) (actual time=6,162.078..6,180.673 rows=200 loops=1)

  • Join Filter: (((est.cd_prov_ent)::text = (h3.cd_prov_ent)::text) AND ((est.cd_mun_ent)::text = (h3.cd_mun_ent)::text) AND ((est.cd_estdo)::text = (h3.pos_cd_estdo)::text))
  • Rows Removed by Join Filter: 3589
10. 0.225 6,178.579 ↓ 200.0 200 1

Nested Loop (cost=104,117.68..135,253.09 rows=1 width=709) (actual time=6,162.060..6,178.579 rows=200 loops=1)

11. 0.270 6,171.542 ↓ 524.0 524 1

GroupAggregate (cost=104,117.26..135,244.62 rows=1 width=19) (actual time=6,162.032..6,171.542 rows=524 loops=1)

12. 2.293 6,171.272 ↓ 534.0 534 1

Merge Join (cost=104,117.26..135,244.60 rows=1 width=19) (actual time=6,162.014..6,171.272 rows=534 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: 2044
13. 0.576 1.098 ↑ 186.6 525 1

GroupAggregate (cost=0.42..23,472.47 rows=97,945 width=19) (actual time=0.026..1.098 rows=525 loops=1)

14. 0.522 0.522 ↑ 184.3 2,575 1

Index Only Scan using phistorico_norden6 on phistorico h (cost=0.42..17,747.05 rows=474,597 width=19) (actual time=0.018..0.522 rows=2,575 loops=1)

  • Index Cond: (fecha_real <= '2018-12-31 00:00:00'::timestamp without time zone)
  • Heap Fetches: 0
15. 0.241 6,167.881 ↑ 184.4 2,578 1

Materialize (cost=104,116.84..106,493.28 rows=475,288 width=27) (actual time=6,161.950..6,167.881 rows=2,578 loops=1)

16. 5,717.870 6,167.640 ↑ 184.4 2,578 1

Sort (cost=104,116.84..105,305.06 rows=475,288 width=27) (actual time=6,161.946..6,167.640 rows=2,578 loops=1)

  • Sort Key: h1.cd_prov_ent, h1.cd_mun_ent, h1.id_habitante
  • Sort Method: external merge Disk: 19472kB
17. 449.770 449.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.035..449.770 rows=475,288 loops=1)

18. 6.812 6.812 ↓ 0.0 0 524

Index Scan using phistorico_norden3 on phistorico h3 (cost=0.42..8.45 rows=1 width=709) (actual time=0.013..0.013 rows=0 loops=524)

  • 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: 1
19. 0.600 0.600 ↓ 19.0 19 200

Seq Scan on pestudio est (cost=0.00..1.28 rows=1 width=56) (actual time=0.001..0.003 rows=19 loops=200)

  • Filter: (((cd_prov_ent)::text = '46'::text) AND ((cd_mun_ent)::text = '131'::text))
20. 0.000 0.000 ↓ 0.0 0 200

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

21. 26.400 26.400 ↑ 1.0 3,124 200

Seq Scan on phtramo pht (cost=0.00..89.36 rows=3,136 width=19) (actual time=0.001..0.132 rows=3,124 loops=200)

22. 0.600 0.600 ↑ 1.0 1 200

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

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
23. 0.200 0.200 ↑ 1.0 22 200

Seq Scan on phunidadpob phun (cost=0.00..1.22 rows=22 width=212) (actual time=0.000..0.001 rows=22 loops=200)

24. 7.200 7.200 ↑ 1.0 829 200

Seq Scan on phvia phv (cost=0.00..22.32 rows=832 width=45) (actual time=0.001..0.036 rows=829 loops=200)

25. 2.400 2.400 ↑ 1.0 281 200

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

26. 0.000 0.000 ↑ 1.0 3 200

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