explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RBgC

Settings
# exclusive inclusive rows x rows loops node
1. 9.333 76,694.562 ↓ 4,249.0 4,249 1

HashAggregate (cost=286,232.87..286,232.88 rows=1 width=4) (actual time=76,694.013..76,694.562 rows=4,249 loops=1)

2. 8,078.861 76,685.229 ↓ 4,249.0 4,249 1

Nested Loop (cost=247,233.83..286,232.87 rows=1 width=4) (actual time=2,553.483..76,685.229 rows=4,249 loops=1)

  • Join Filter: (ph.cd_hoja = ph_1.cd_hoja)
  • Rows Removed by Join Filter: 177184098
3. 21.197 2,009.740 ↓ 17,507.0 17,507 1

Nested Loop (cost=104,117.42..143,116.42 rows=1 width=8) (actual time=553.196..2,009.740 rows=17,507 loops=1)

4. 32.532 1,883.501 ↓ 17,507.0 17,507 1

Nested Loop (cost=104,117.12..143,112.06 rows=1 width=12) (actual time=553.190..1,883.501 rows=17,507 loops=1)

5. 56.337 1,279.005 ↓ 142,991.0 142,991 1

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

6. 136.166 1,222.668 ↓ 144,961.0 144,961 1

Merge Join (cost=104,116.70..143,103.57 rows=1 width=12) (actual time=553.139..1,222.668 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
7. 130.130 285.861 ↓ 1.4 142,991 1

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

8. 155.731 155.731 ↓ 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=0.067..155.731 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
9. 52.476 800.641 ↓ 1.0 475,289 1

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

10. 422.241 748.165 ↓ 1.0 475,289 1

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

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

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

12. 571.964 571.964 ↓ 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.004..0.004 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))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
13. 105.042 105.042 ↑ 1.0 1 17,507

Index Scan using phhojapadronal_pkey on phhojapadronal ph (cost=0.29..4.35 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=17,507)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
14. 64,653.670 66,596.628 ↓ 10,121.0 10,121 17,507

HashAggregate (cost=143,116.41..143,116.43 rows=1 width=4) (actual time=0.114..3.804 rows=10,121 loops=17,507)

  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 20892
15. 60.781 1,942.958 ↓ 75,793.0 75,793 1

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

16. 47.792 1,730.591 ↓ 75,793.0 75,793 1

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

17. 45.614 1,110.835 ↓ 142,991.0 142,991 1

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

18. 111.623 1,065.221 ↓ 144,961.0 144,961 1

Merge Join (cost=104,116.70..143,103.57 rows=1 width=12) (actual time=572.270..1,065.221 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
19. 111.740 240.595 ↓ 1.4 142,991 1

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

20. 128.855 128.855 ↓ 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.042..128.855 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
21. 36.553 713.003 ↓ 1.0 475,289 1

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

22. 337.983 676.450 ↓ 1.0 475,289 1

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

  • Sort Key: h1_1.id_habitante
  • Sort Method: external merge Disk: 19016kB
23. 338.467 338.467 ↓ 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.010..338.467 rows=475,289 loops=1)

24. 571.964 571.964 ↑ 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.004..0.004 rows=1 loops=142,991)

  • Index Cond: (id_registro = (max(h1_1.id_registro)))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
25. 151.586 151.586 ↑ 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.002..0.002 rows=1 loops=75,793)

  • Index Cond: (id_registro = h3_1.pos_id_hoja_hist)