explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EHBi

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 6,394.339 ↓ 14.0 14 1

Unique (cost=60,394.73..60,394.74 rows=1 width=4) (actual time=6,394.336..6,394.339 rows=14 loops=1)

2. 0.027 6,394.336 ↓ 14.0 14 1

Sort (cost=60,394.73..60,394.74 rows=1 width=4) (actual time=6,394.335..6,394.336 rows=14 loops=1)

  • Sort Key: h3.id_habitante
  • Sort Method: quicksort Memory: 25kB
3. 590.391 6,394.309 ↓ 14.0 14 1

Nested Loop (cost=60,386.51..60,394.72 rows=1 width=4) (actual time=1,621.499..6,394.309 rows=14 loops=1)

  • Join Filter: (ph.cd_hoja = ph_1.cd_hoja)
  • Rows Removed by Join Filter: 13042491
4. 0.817 662.388 ↓ 4,717.0 4,717 1

Nested Loop (cost=30,189.20..30,197.38 rows=1 width=8) (actual time=493.381..662.388 rows=4,717 loops=1)

5. 16.947 647.420 ↓ 4,717.0 4,717 1

Nested Loop (cost=30,188.92..30,196.97 rows=1 width=12) (actual time=493.374..647.420 rows=4,717 loops=1)

6. 28.913 507.341 ↓ 41,044.0 41,044 1

HashAggregate (cost=30,188.50..30,188.51 rows=1 width=12) (actual time=493.346..507.341 rows=41,044 loops=1)

7. 308.639 478.428 ↓ 41,694.0 41,694 1

Hash Join (cost=14,624.25..30,188.49 rows=1 width=12) (actual time=106.323..478.428 rows=41,694 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. 2.738 63.678 ↓ 1.2 41,044 1

Subquery Scan on max1 (cost=0.42..8,051.95 rows=34,165 width=19) (actual time=0.047..63.678 rows=41,044 loops=1)

9. 28.949 60.940 ↓ 1.2 41,044 1

GroupAggregate (cost=0.42..7,710.30 rows=34,165 width=19) (actual time=0.046..60.940 rows=41,044 loops=1)

10. 31.991 31.991 ↑ 1.0 140,561 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h (cost=0.42..5,963.04 rows=140,561 width=19) (actual time=0.042..31.991 rows=140,561 loops=1)

  • Index Cond: ((cd_prov_ent = '36'::text) AND (cd_mun_ent = '008'::text) AND (pos_fecha_real_ult <= '2019-09-02 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
11. 38.798 106.111 ↑ 1.0 140,561 1

Hash (cost=10,851.61..10,851.61 rows=140,561 width=27) (actual time=106.111..106.111 rows=140,561 loops=1)

  • Buckets: 2048 Batches: 16 Memory Usage: 552kB
12. 67.313 67.313 ↑ 1.0 140,561 1

Seq Scan on phistorico h1 (cost=0.00..10,851.61 rows=140,561 width=27) (actual time=0.003..67.313 rows=140,561 loops=1)

13. 123.132 123.132 ↓ 0.0 0 41,044

Index Scan using indx_phco_id_prov_mun on phistorico h3 (cost=0.42..8.45 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=41,044)

  • Index Cond: (id_registro = (max(h1.id_registro)))
  • Filter: ((tipo_mov <> 'B'::bpchar) AND (pos_fecha_nac > '1999-03-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
14. 14.151 14.151 ↑ 1.0 1 4,717

Index Scan using phhojapadronal_pkey on phhojapadronal ph (cost=0.29..0.40 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=4,717)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
15. 4,455.492 5,141.530 ↓ 2,765.0 2,765 4,717

HashAggregate (cost=30,197.31..30,197.32 rows=1 width=4) (actual time=0.148..1.090 rows=2,765 loops=4,717)

  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 7570
16. 15.829 686.038 ↓ 26,682.0 26,682 1

Nested Loop (cost=30,189.20..30,197.30 rows=1 width=4) (actual time=490.289..686.038 rows=26,682 loops=1)

17. 18.576 643.527 ↓ 26,682.0 26,682 1

Nested Loop (cost=30,188.92..30,196.97 rows=1 width=8) (actual time=490.280..643.527 rows=26,682 loops=1)

18. 26.723 501.819 ↓ 41,044.0 41,044 1

HashAggregate (cost=30,188.50..30,188.51 rows=1 width=12) (actual time=490.252..501.819 rows=41,044 loops=1)

19. 307.312 475.096 ↓ 41,694.0 41,694 1

Hash Join (cost=14,624.25..30,188.49 rows=1 width=12) (actual time=103.959..475.096 rows=41,694 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. 2.828 64.066 ↓ 1.2 41,044 1

Subquery Scan on max1_1 (cost=0.42..8,051.95 rows=34,165 width=19) (actual time=0.070..64.066 rows=41,044 loops=1)

21. 29.110 61.238 ↓ 1.2 41,044 1

GroupAggregate (cost=0.42..7,710.30 rows=34,165 width=19) (actual time=0.069..61.238 rows=41,044 loops=1)

22. 32.128 32.128 ↑ 1.0 140,561 1

Index Only Scan using idx_phco_habpk_idreg_posfreal on phistorico h_1 (cost=0.42..5,963.04 rows=140,561 width=19) (actual time=0.044..32.128 rows=140,561 loops=1)

  • Index Cond: ((cd_prov_ent = '36'::text) AND (cd_mun_ent = '008'::text) AND (pos_fecha_real_ult <= '2019-09-02 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
23. 37.896 103.718 ↑ 1.0 140,561 1

Hash (cost=10,851.61..10,851.61 rows=140,561 width=27) (actual time=103.718..103.718 rows=140,561 loops=1)

  • Buckets: 2048 Batches: 16 Memory Usage: 552kB
24. 65.822 65.822 ↑ 1.0 140,561 1

Seq Scan on phistorico h1_1 (cost=0.00..10,851.61 rows=140,561 width=27) (actual time=0.003..65.822 rows=140,561 loops=1)

25. 123.132 123.132 ↑ 1.0 1 41,044

Index Scan using indx_phco_id_prov_mun on phistorico h3_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=41,044)

  • Index Cond: ((id_registro = (max(h1_1.id_registro))) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '008'::text))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
26. 26.682 26.682 ↑ 1.0 1 26,682

Index Scan using phhojapadronal_pkey on phhojapadronal ph_1 (cost=0.29..0.32 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=26,682)

  • Index Cond: (id_registro = h3_1.pos_id_hoja_hist)