explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xv6h

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 10,938.614 ↓ 14.0 14 1

Unique (cost=103,325.21..103,325.22 rows=1 width=4) (actual time=10,938.611..10,938.614 rows=14 loops=1)

2. 0.031 10,938.611 ↓ 14.0 14 1

Sort (cost=103,325.21..103,325.22 rows=1 width=4) (actual time=10,938.610..10,938.611 rows=14 loops=1)

  • Sort Key: h3.id_habitante
  • Sort Method: quicksort Memory: 25kB
3. 595.687 10,938.580 ↓ 14.0 14 1

Nested Loop (cost=103,316.99..103,325.20 rows=1 width=4) (actual time=5,850.991..10,938.580 rows=14 loops=1)

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

Nested Loop (cost=51,654.45..51,662.62 rows=1 width=8) (actual time=2,594.395..2,781.542 rows=4,717 loops=1)

5. 0.000 2,763.790 ↓ 4,717.0 4,717 1

Nested Loop (cost=51,654.16..51,662.21 rows=1 width=12) (actual time=2,594.388..2,763.790 rows=4,717 loops=1)

6. 31.534 2,610.481 ↓ 41,044.0 41,044 1

HashAggregate (cost=51,653.74..51,653.75 rows=1 width=12) (actual time=2,594.357..2,610.481 rows=41,044 loops=1)

7. 310.132 2,578.947 ↓ 41,694.0 41,694 1

Hash Join (cost=41,432.20..51,653.73 rows=1 width=12) (actual time=2,139.736..2,578.947 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. 3.573 2,163.333 ↓ 1.2 41,044 1

Subquery Scan on max1 (cost=26,808.37..29,257.82 rows=34,622 width=19) (actual time=2,034.080..2,163.333 rows=41,044 loops=1)

9. 35.127 2,159.760 ↓ 1.2 41,044 1

GroupAggregate (cost=26,808.37..28,911.60 rows=34,622 width=19) (actual time=2,034.079..2,159.760 rows=41,044 loops=1)

10. 2,039.424 2,124.633 ↑ 1.0 140,561 1

Sort (cost=26,808.37..27,159.77 rows=140,561 width=19) (actual time=2,034.069..2,124.633 rows=140,561 loops=1)

  • Sort Key: h.cd_prov_ent, h.cd_mun_ent, h.id_habitante
  • Sort Method: external merge Disk: 4672kB
11. 85.209 85.209 ↑ 1.0 140,561 1

Seq Scan on phistorico h (cost=0.00..11,905.82 rows=140,561 width=19) (actual time=0.008..85.209 rows=140,561 loops=1)

  • Filter: ((pos_fecha_real_ult <= '2019-09-02 00:00:00'::timestamp without time zone) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '008'::text))
12. 38.480 105.482 ↑ 1.0 140,561 1

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

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

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

14. 164.176 164.176 ↓ 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.004 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
15. 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.003..0.003 rows=1 loops=4,717)

  • Index Cond: (id_registro = h3.pos_id_hoja_hist)
16. 4,753.989 7,561.351 ↓ 2,765.0 2,765 4,717

HashAggregate (cost=51,662.55..51,662.56 rows=1 width=4) (actual time=0.598..1.603 rows=2,765 loops=4,717)

  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 7570
17. 18.033 2,807.362 ↓ 26,682.0 26,682 1

Nested Loop (cost=51,654.45..51,662.54 rows=1 width=4) (actual time=2,632.669..2,807.362 rows=26,682 loops=1)

18. 0.000 2,762.647 ↓ 26,682.0 26,682 1

Nested Loop (cost=51,654.16..51,662.21 rows=1 width=8) (actual time=2,632.662..2,762.647 rows=26,682 loops=1)

19. 28.503 2,644.638 ↓ 41,044.0 41,044 1

HashAggregate (cost=51,653.74..51,653.75 rows=1 width=12) (actual time=2,632.641..2,644.638 rows=41,044 loops=1)

20. 324.684 2,616.135 ↓ 41,694.0 41,694 1

Hash Join (cost=41,432.20..51,653.73 rows=1 width=12) (actual time=2,160.394..2,616.135 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. (...)
21. 3.429 2,185.249 ↓ 1.2 41,044 1

Subquery Scan on max1_1 (cost=26,808.37..29,257.82 rows=34,622 width=19) (actual time=2,054.023..2,185.249 rows=41,044 loops=1)

22. 35.805 2,181.820 ↓ 1.2 41,044 1

GroupAggregate (cost=26,808.37..28,911.60 rows=34,622 width=19) (actual time=2,054.022..2,181.820 rows=41,044 loops=1)

23. 2,059.055 2,146.015 ↑ 1.0 140,561 1

Sort (cost=26,808.37..27,159.77 rows=140,561 width=19) (actual time=2,054.011..2,146.015 rows=140,561 loops=1)

  • Sort Key: h_1.cd_prov_ent, h_1.cd_mun_ent, h_1.id_habitante
  • Sort Method: external merge Disk: 4672kB
24. 86.960 86.960 ↑ 1.0 140,561 1

Seq Scan on phistorico h_1 (cost=0.00..11,905.82 rows=140,561 width=19) (actual time=0.007..86.960 rows=140,561 loops=1)

  • Filter: ((pos_fecha_real_ult <= '2019-09-02 00:00:00'::timestamp without time zone) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '008'::text))
25. 38.816 106.202 ↑ 1.0 140,561 1

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

  • Buckets: 2048 Batches: 16 Memory Usage: 552kB
26. 67.386 67.386 ↑ 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..67.386 rows=140,561 loops=1)

27. 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.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=41,044)

  • Index Cond: (id_registro = (max(h1_1.id_registro)))
  • Filter: (tipo_mov <> 'B'::bpchar)
  • Rows Removed by Filter: 0
28. 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)