explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OB26n

Settings
# exclusive inclusive rows x rows loops node
1. 0.440 28,268.029 ↓ 4,247.0 4,247 1

Unique (cost=283,940.88..283,940.88 rows=1 width=4) (actual time=28,267.335..28,268.029 rows=4,247 loops=1)

2. 3.683 28,267.589 ↓ 4,247.0 4,247 1

Sort (cost=283,940.88..283,940.88 rows=1 width=4) (actual time=28,267.334..28,267.589 rows=4,247 loops=1)

  • Sort Key: h3.id_habitante
  • Sort Method: quicksort Memory: 392kB
3. 8,714.804 28,263.906 ↓ 4,247.0 4,247 1

Nested Loop (cost=283,932.77..283,940.87 rows=1 width=4) (actual time=3,958.405..28,263.906 rows=4,247 loops=1)

  • Join Filter: (h3.pos_id_hoja_hist = ph2.id_registro)
  • Rows Removed by Join Filter: 191456693
4. 0.000 1,855.591 ↓ 17,501.0 17,501 1

Nested Loop (cost=141,955.94..141,964.00 rows=1 width=12) (actual time=1,099.303..1,855.591 rows=17,501 loops=1)

5. 114.760 1,154.976 ↓ 142,991.0 142,991 1

HashAggregate (cost=141,955.52..141,955.53 rows=1 width=12) (actual time=1,099.250..1,154.976 rows=142,991 loops=1)

6. 370.095 1,040.216 ↓ 144,961.0 144,961 1

Hash Join (cost=49,308.53..141,955.52 rows=1 width=12) (actual time=440.624..1,040.216 rows=144,961 loops=1)

  • Hash Cond: ((max1.max_fecha_real = h1.pos_fecha_real_ult) AND ((max1.cd_prov_ent)::text = (h1.cd_prov_ent)::text) AND ((max1.cd_mun_ent)::text = (h1.cd_mun_ent)::text) AND (max1.id_habitante = h1.id_habitante))
7. 9.726 230.063 ↓ 1.4 142,991 1

Subquery Scan on max1 (cost=0.42..26,879.92 rows=99,752 width=19) (actual time=0.073..230.063 rows=142,991 loops=1)

8. 98.671 220.337 ↓ 1.4 142,991 1

GroupAggregate (cost=0.42..25,882.40 rows=99,752 width=19) (actual time=0.073..220.337 rows=142,991 loops=1)

9. 121.666 121.666 ↑ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h (cost=0.42..20,131.51 rows=475,337 width=19) (actual time=0.066..121.666 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. 133.938 440.058 ↑ 1.0 475,289 1

Hash (cost=36,551.37..36,551.37 rows=475,337 width=27) (actual time=440.058..440.058 rows=475,289 loops=1)

  • Buckets: 2048 Batches: 32 Memory Usage: 938kB
11. 306.120 306.120 ↑ 1.0 475,289 1

Seq Scan on phistorico h1 (cost=0.00..36,551.37 rows=475,337 width=27) (actual time=0.010..306.120 rows=475,289 loops=1)

12. 714.955 714.955 ↓ 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.005..0.005 rows=0 loops=142,991)

  • Index Cond: ((id_registro = (max(h1.id_registro))) AND ((cd_prov_ent)::text = '46'::text) AND ((cd_mun_ent)::text = '131'::text) 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. 14,838.133 17,693.511 ↓ 10,940.0 10,940 17,501

HashAggregate (cost=141,976.83..141,976.84 rows=1 width=8) (actual time=0.164..1.011 rows=10,940 loops=17,501)

14. 1.612 2,855.378 ↓ 10,940.0 10,940 1

Nested Loop (cost=141,968.77..141,976.83 rows=1 width=8) (actual time=2,725.197..2,855.378 rows=10,940 loops=1)

15. 52.011 2,732.314 ↓ 10,121.0 10,121 1

HashAggregate (cost=141,968.35..141,968.37 rows=1 width=4) (actual time=2,725.157..2,732.314 rows=10,121 loops=1)

  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 20892
16. 22.897 2,680.303 ↓ 75,793.0 75,793 1

Nested Loop (cost=141,956.24..141,968.35 rows=1 width=4) (actual time=1,132.812..2,680.303 rows=75,793 loops=1)

17. 27.279 2,354.234 ↓ 75,793.0 75,793 1

Nested Loop (cost=141,955.94..141,964.00 rows=1 width=8) (actual time=1,132.806..2,354.234 rows=75,793 loops=1)

18. 107.621 1,183.027 ↓ 142,991.0 142,991 1

HashAggregate (cost=141,955.52..141,955.53 rows=1 width=12) (actual time=1,132.752..1,183.027 rows=142,991 loops=1)

19. 415.750 1,075.406 ↓ 144,961.0 144,961 1

Hash Join (cost=49,308.53..141,955.52 rows=1 width=12) (actual time=443.309..1,075.406 rows=144,961 loops=1)

  • Hash Cond: ((max1_1.max_fecha_real = h1_1.pos_fecha_real_ult) AND ((max1_1.cd_prov_ent)::text = (h1_1.cd_prov_ent)::text) AND ((max1_1.cd_mun_ent)::text = (h1_1.cd_mun_ent)::text) AND (max1_1.id_habit (...)
20. 9.687 216.868 ↓ 1.4 142,991 1

Subquery Scan on max1_1 (cost=0.42..26,879.92 rows=99,752 width=19) (actual time=0.049..216.868 rows=142,991 loops=1)

21. 98.898 207.181 ↓ 1.4 142,991 1

GroupAggregate (cost=0.42..25,882.40 rows=99,752 width=19) (actual time=0.048..207.181 rows=142,991 loops=1)

22. 108.283 108.283 ↑ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h_1 (cost=0.42..20,131.51 rows=475,337 width=19) (actual time=0.040..108.283 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
23. 133.950 442.788 ↑ 1.0 475,289 1

Hash (cost=36,551.37..36,551.37 rows=475,337 width=27) (actual time=442.788..442.788 rows=475,289 loops=1)

  • Buckets: 2048 Batches: 32 Memory Usage: 938kB
24. 308.838 308.838 ↑ 1.0 475,289 1

Seq Scan on phistorico h1_1 (cost=0.00..36,551.37 rows=475,337 width=27) (actual time=0.011..308.838 rows=475,289 loops=1)

25. 1,143.928 1,143.928 ↑ 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.008..0.008 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. 303.172 303.172 ↑ 1.0 1 75,793

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

  • Index Cond: (id_registro = h3_1.pos_id_hoja_hist)
27. 121.452 121.452 ↑ 1.0 1 10,121

Index Scan using idx_phhoja on phhojapadronal ph2 (cost=0.42..8.44 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=10,121)

  • Index Cond: (((cd_prov_ent)::text = '46'::text) AND ((cd_mun_ent)::text = '131'::text) AND (cd_hoja = ph.cd_hoja))