explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p6JY

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 10,900.576 ↓ 153.0 153 1

Unique (cost=278,494.31..278,494.31 rows=1 width=4) (actual time=10,900.554..10,900.576 rows=153 loops=1)

2. 0.282 10,900.560 ↓ 153.0 153 1

Sort (cost=278,494.31..278,494.31 rows=1 width=4) (actual time=10,900.553..10,900.560 rows=153 loops=1)

  • Sort Key: h3.id_habitante
  • Sort Method: quicksort Memory: 32kB
3. 477.159 10,900.278 ↓ 153.0 153 1

Nested Loop (cost=278,481.92..278,494.30 rows=1 width=4) (actual time=3,655.426..10,900.278 rows=153 loops=1)

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

Nested Loop (cost=139,234.90..139,247.24 rows=1 width=8) (actual time=1,103.944..2,404.323 rows=996 loops=1)

5. 79.613 2,388.254 ↓ 996.0 996 1

Nested Loop (cost=139,234.61..139,242.67 rows=1 width=12) (actual time=1,103.931..2,388.254 rows=996 loops=1)

6. 118.453 1,164.713 ↓ 142,991.0 142,991 1

HashAggregate (cost=139,234.19..139,234.20 rows=1 width=12) (actual time=1,103.711..1,164.713 rows=142,991 loops=1)

7. 380.304 1,046.260 ↓ 144,961.0 144,961 1

Hash Join (cost=49,304.50..139,234.18 rows=1 width=12) (actual time=435.429..1,046.260 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))
8. 9.717 231.356 ↓ 1.5 142,991 1

Subquery Scan on max1 (cost=0.42..26,838.09 rows=97,825 width=19) (actual time=0.074..231.356 rows=142,991 loops=1)

9. 100.272 221.639 ↓ 1.5 142,991 1

GroupAggregate (cost=0.42..25,859.84 rows=97,825 width=19) (actual time=0.073..221.639 rows=142,991 loops=1)

10. 121.367 121.367 ↓ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h (cost=0.42..20,129.23 rows=475,236 width=19) (actual time=0.066..121.367 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
11. 136.477 434.600 ↓ 1.0 475,289 1

Hash (cost=36,550.36..36,550.36 rows=475,236 width=27) (actual time=434.600..434.600 rows=475,289 loops=1)

  • Buckets: 2048 Batches: 32 Memory Usage: 938kB
12. 298.123 298.123 ↓ 1.0 475,289 1

Seq Scan on phistorico h1 (cost=0.00..36,550.36 rows=475,236 width=27) (actual time=0.012..298.123 rows=475,289 loops=1)

13. 1,143.928 1,143.928 ↓ 0.0 0 142,991

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

  • Index Cond: (id_registro = (max(h1.id_registro)))
  • Filter: ((tipo_mov <> 'B'::bpchar) 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))
  • Rows Removed by Filter: 1
14. 13.944 13.944 ↑ 1.0 1 996

Index Scan using phhojapadronal_pkey on phhojapadronal ph (cost=0.29..4.56 rows=1 width=12) (actual time=0.011..0.014 rows=1 loops=996)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
15. 5,517.099 8,018.796 ↓ 10,121.0 10,121 996

HashAggregate (cost=139,247.02..139,247.03 rows=1 width=4) (actual time=2.558..8.051 rows=10,121 loops=996)

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

Nested Loop (cost=139,234.90..139,247.02 rows=1 width=4) (actual time=1,109.583..2,501.697 rows=75,793 loops=1)

17. 0.000 2,301.840 ↓ 75,793.0 75,793 1

Nested Loop (cost=139,234.61..139,242.66 rows=1 width=8) (actual time=1,109.549..2,301.840 rows=75,793 loops=1)

18. 109.257 1,161.153 ↓ 142,991.0 142,991 1

HashAggregate (cost=139,234.19..139,234.20 rows=1 width=12) (actual time=1,109.516..1,161.153 rows=142,991 loops=1)

19. 392.883 1,051.896 ↓ 144,961.0 144,961 1

Hash Join (cost=49,304.50..139,234.18 rows=1 width=12) (actual time=443.420..1,051.896 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_habitante = h1_1. (...)
20. 9.587 216.287 ↓ 1.5 142,991 1

Subquery Scan on max1_1 (cost=0.42..26,838.09 rows=97,825 width=19) (actual time=0.053..216.287 rows=142,991 loops=1)

21. 98.100 206.700 ↓ 1.5 142,991 1

GroupAggregate (cost=0.42..25,859.84 rows=97,825 width=19) (actual time=0.052..206.700 rows=142,991 loops=1)

22. 108.600 108.600 ↓ 1.0 475,289 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h_1 (cost=0.42..20,129.23 rows=475,236 width=19) (actual time=0.045..108.600 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. 137.545 442.726 ↓ 1.0 475,289 1

Hash (cost=36,550.36..36,550.36 rows=475,236 width=27) (actual time=442.726..442.726 rows=475,289 loops=1)

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

Seq Scan on phistorico h1_1 (cost=0.00..36,550.36 rows=475,236 width=27) (actual time=0.010..305.181 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 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. 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)