explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YXwD

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 13,852.743 ↓ 153.0 153 1

Unique (cost=286,233.10..286,233.11 rows=1 width=4) (actual time=13,852.723..13,852.743 rows=153 loops=1)

2. 0.195 13,852.727 ↓ 153.0 153 1

Sort (cost=286,233.10..286,233.11 rows=1 width=4) (actual time=13,852.723..13,852.727 rows=153 loops=1)

  • Sort Key: h3.id_habitante
  • Sort Method: quicksort Memory: 32kB
3. 464.973 13,852.532 ↓ 153.0 153 1

Nested Loop (cost=247,233.83..286,233.09 rows=1 width=4) (actual time=8,113.958..13,852.532 rows=153 loops=1)

  • Join Filter: (ph.cd_hoja = ph_1.cd_hoja)
  • Rows Removed by Join Filter: 10080363
4. 1.035 2,483.351 ↓ 996.0 996 1

Nested Loop (cost=104,117.42..143,116.64 rows=1 width=8) (actual time=1,643.162..2,483.351 rows=996 loops=1)

5. 0.000 2,476.340 ↓ 996.0 996 1

Nested Loop (cost=104,117.12..143,112.06 rows=1 width=12) (actual time=1,643.143..2,476.340 rows=996 loops=1)

6. 40.599 2,200.869 ↓ 142,991.0 142,991 1

GroupAggregate (cost=104,116.70..143,103.59 rows=1 width=12) (actual time=1,635.241..2,200.869 rows=142,991 loops=1)

7. 105.524 2,160.270 ↓ 144,961.0 144,961 1

Merge Join (cost=104,116.70..143,103.57 rows=1 width=12) (actual time=1,635.228..2,160.270 rows=144,961 loops=1)

  • Merge Cond: (h.id_habitante = h1.id_habitante)
  • Join Filter: ((h1.pos_fecha_real_ult = (max(h.pos_fecha_real_ult))) AND ((h1.cd_prov_ent)::text = (h.cd_prov_ent)::text) AND ((h1.cd_mun_ent)::text = (h.cd_mun_ent)::text))
  • Rows Removed by Join Filter: 330328
8. 103.488 262.364 ↓ 1.4 142,991 1

GroupAggregate (cost=0.42..25,870.72 rows=98,760 width=19) (actual time=19.737..262.364 rows=142,991 loops=1)

9. 158.876 158.876 ↓ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h (cost=0.42..20,130.29 rows=475,283 width=19) (actual time=19.726..158.876 rows=475,289 loops=1)

  • Index Cond: ((cd_prov_ent = '46'::text) AND (cd_mun_ent = '131'::text) AND (pos_fecha_real_ult <= '2019-09-11 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
10. 39.984 1,792.382 ↓ 1.0 475,289 1

Materialize (cost=104,116.28..106,492.69 rows=475,283 width=27) (actual time=1,615.475..1,792.382 rows=475,289 loops=1)

11. 376.637 1,752.398 ↓ 1.0 475,289 1

Sort (cost=104,116.28..105,304.49 rows=475,283 width=27) (actual time=1,615.468..1,752.398 rows=475,289 loops=1)

  • Sort Key: h1.id_habitante
  • Sort Method: external merge Disk: 19016kB
12. 1,375.761 1,375.761 ↓ 1.0 475,289 1

Seq Scan on phistorico h1 (cost=0.00..36,550.83 rows=475,283 width=27) (actual time=0.019..1,375.761 rows=475,289 loops=1)

13. 285.982 285.982 ↓ 0.0 0 142,991

Index Scan using indx_phco_id_prov_mun_tipo_fecha_nac on phistorico h3 (cost=0.42..8.45 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=142,991)

  • Index Cond: ((id_registro = (max(h1.id_registro))) AND (pos_fecha_nac >= '1958-09-12 00:00:00'::timestamp without time zone) AND (pos_fecha_nac <= '1959-09-11 00:00:00'::timestamp without time zone))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
14. 5.976 5.976 ↑ 1.0 1 996

Index Scan using phhojapadronal_pkey on phhojapadronal ph (cost=0.29..4.57 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=996)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
15. 4,587.962 10,904.208 ↓ 10,121.0 10,121 996

HashAggregate (cost=143,116.41..143,116.43 rows=1 width=4) (actual time=6.384..10.948 rows=10,121 loops=996)

  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 20892
16. 3.896 6,316.246 ↓ 75,793.0 75,793 1

Nested Loop (cost=104,117.42..143,116.41 rows=1 width=4) (actual time=694.145..6,316.246 rows=75,793 loops=1)

17. 21.659 5,630.213 ↓ 75,793.0 75,793 1

Nested Loop (cost=104,117.12..143,112.05 rows=1 width=8) (actual time=688.183..5,630.213 rows=75,793 loops=1)

18. 46.271 1,318.824 ↓ 142,991.0 142,991 1

GroupAggregate (cost=104,116.70..143,103.59 rows=1 width=12) (actual time=688.150..1,318.824 rows=142,991 loops=1)

19. 115.637 1,272.553 ↓ 144,961.0 144,961 1

Merge Join (cost=104,116.70..143,103.57 rows=1 width=12) (actual time=688.134..1,272.553 rows=144,961 loops=1)

  • Merge Cond: (h_1.id_habitante = h1_1.id_habitante)
  • Join Filter: ((h1_1.pos_fecha_real_ult = (max(h_1.pos_fecha_real_ult))) AND ((h1_1.cd_prov_ent)::text = (h_1.cd_prov_ent)::text) AND ((h1_1.cd_mun_ent)::text = (h_1.cd_mun_ent)::text))
  • Rows Removed by Join Filter: 330328
20. 113.276 301.053 ↓ 1.4 142,991 1

GroupAggregate (cost=0.42..25,870.72 rows=98,760 width=19) (actual time=0.049..301.053 rows=142,991 loops=1)

21. 187.777 187.777 ↓ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h_1 (cost=0.42..20,130.29 rows=475,283 width=19) (actual time=0.043..187.777 rows=475,289 loops=1)

  • Index Cond: ((cd_prov_ent = '46'::text) AND (cd_mun_ent = '131'::text) AND (pos_fecha_real_ult <= '2019-09-11 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
22. 41.397 855.863 ↓ 1.0 475,289 1

Materialize (cost=104,116.28..106,492.69 rows=475,283 width=27) (actual time=688.070..855.863 rows=475,289 loops=1)

23. 353.974 814.466 ↓ 1.0 475,289 1

Sort (cost=104,116.28..105,304.49 rows=475,283 width=27) (actual time=688.063..814.466 rows=475,289 loops=1)

  • Sort Key: h1_1.id_habitante
  • Sort Method: external merge Disk: 19016kB
24. 460.492 460.492 ↓ 1.0 475,289 1

Seq Scan on phistorico h1_1 (cost=0.00..36,550.83 rows=475,283 width=27) (actual time=0.012..460.492 rows=475,289 loops=1)

25. 4,289.730 4,289.730 ↑ 1.0 1 142,991

Index Scan using indx_phco_id_prov_mun_tipo_fecha_nac on phistorico h3_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.030..0.030 rows=1 loops=142,991)

  • Index Cond: (id_registro = (max(h1_1.id_registro)))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
26. 682.137 682.137 ↑ 1.0 1 75,793

Index Scan using phhojapadronal_pkey on phhojapadronal ph_1 (cost=0.29..4.34 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=75,793)

  • Index Cond: (id_registro = h3_1.pos_id_hoja_hist)