explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yjn1

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 7,561.366 ↓ 200.0 200 1

Limit (cost=104,117.97..135,450.64 rows=1 width=1,049) (actual time=7,404.893..7,561.366 rows=200 loops=1)

2. 0.851 7,561.341 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,450.64 rows=1 width=1,049) (actual time=7,404.892..7,561.341 rows=200 loops=1)

  • Join Filter: (pth.cd_tipo_hoja = phh.cd_tipo_hoja)
  • Rows Removed by Join Filter: 398
3. 4.957 7,560.490 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,449.57 rows=1 width=1,043) (actual time=7,404.885..7,560.490 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.607 7,552.933 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,438.84 rows=1 width=1,049) (actual time=7,404.843..7,552.933 rows=200 loops=1)

  • Join Filter: (phv.id_registro = h3.pos_id_via_hist)
  • Rows Removed by Join Filter: 165566
5. 1.104 7,536.126 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.97..135,406.12 rows=1 width=1,012) (actual time=7,404.818..7,536.126 rows=200 loops=1)

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

Nested Loop Left Join (cost=104,117.97..135,404.63 rows=1 width=808) (actual time=7,404.810..7,534.822 rows=200 loops=1)

7. 34.883 7,534.069 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,400.27 rows=1 width=756) (actual time=7,404.799..7,534.069 rows=200 loops=1)

  • Join Filter: (pht.id_registro = h3.pos_id_tramo_hist)
  • Rows Removed by Join Filter: 624557
8. 0.675 7,472.386 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,271.71 rows=1 width=745) (actual time=7,404.725..7,472.386 rows=200 loops=1)

  • Join Filter: (psv.id_registro = h3.pos_id_pseudovia_hist)
9. 1.209 7,471.711 ↓ 200.0 200 1

Nested Loop Left Join (cost=104,117.68..135,254.51 rows=1 width=721) (actual time=7,404.720..7,471.711 rows=200 loops=1)

  • Join Filter: ((est.cd_estdo)::text = (h3.pos_cd_estdo)::text)
  • Rows Removed by Join Filter: 3589
10. 0.280 7,470.302 ↓ 200.0 200 1

Nested Loop (cost=104,117.68..135,253.09 rows=1 width=709) (actual time=7,404.705..7,470.302 rows=200 loops=1)

11. 0.283 7,427.578 ↓ 524.0 524 1

GroupAggregate (cost=104,117.26..135,244.62 rows=1 width=19) (actual time=7,389.281..7,427.578 rows=524 loops=1)

12. 2.322 7,427.295 ↓ 534.0 534 1

Merge Join (cost=104,117.26..135,244.60 rows=1 width=19) (actual time=7,389.263..7,427.295 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.676 62.641 ↑ 186.6 525 1

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

14. 61.965 61.965 ↑ 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=32.855..61.965 rows=2,575 loops=1)

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

Materialize (cost=104,116.84..106,493.28 rows=475,288 width=27) (actual time=7,356.288..7,362.332 rows=2,578 loops=1)

16. 5,825.270 7,362.081 ↑ 184.4 2,578 1

Sort (cost=104,116.84..105,305.06 rows=475,288 width=27) (actual time=7,356.283..7,362.081 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. 1,536.811 1,536.811 ↑ 1.0 475,288 1

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

18. 42.444 42.444 ↓ 0.0 0 524

Index Scan using phistorico_norden3 on phistorico h3 (cost=0.42..8.45 rows=1 width=709) (actual time=0.081..0.081 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.200 0.200 ↑ 1.0 19 200

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

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.800 26.800 ↑ 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.134 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.003..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.600 2.600 ↑ 1.0 281 200

Seq Scan on ptipovia tpv (cost=0.00..5.81 rows=281 width=16) (actual time=0.001..0.013 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)