explain.depesz.com

PostgreSQL's explain analyze made readable

Result: osky

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 9,103.892 ↓ 200.0 200 1

Limit (cost=456,596.97..456,596.98 rows=1 width=1,162) (actual time=9,103.866..9,103.892 rows=200 loops=1)

2. 50.663 9,103.883 ↓ 200.0 200 1

Sort (cost=456,596.97..456,596.98 rows=1 width=1,162) (actual time=9,103.865..9,103.883 rows=200 loops=1)

  • Sort Key: phistorico0_.id_habitante, phistorico0_.id_registro
  • Sort Method: top-N heapsort Memory: 228kB
3. 3,211.979 9,053.220 ↓ 17,727.0 17,727 1

Nested Loop (cost=428,039.33..456,596.96 rows=1 width=1,162) (actual time=8,783.065..9,053.220 rows=17,727 loops=1)

  • Join Filter: (((phvia6_.cd_prov_ent)::text = (ptipovia7_.cd_prov_ent)::text) AND ((phvia6_.cd_mun_ent)::text = (ptipovia7_.cd_mun_ent)::text) AND (phvia6_.cd_tipo_via = ptipovia7_.cd_tipo_via))
  • Rows Removed by Join Filter: 4715382
4. 0.349 0.349 ↑ 1.0 267 1

Seq Scan on ptipovia ptipovia7_ (cost=0.00..5.67 rows=267 width=16) (actual time=0.008..0.349 rows=267 loops=1)

5. 3,598.253 5,840.892 ↓ 151.5 17,727 267

Materialize (cost=428,039.33..455,966.81 rows=117 width=1,168) (actual time=4.795..21.876 rows=17,727 loops=267)

6. 262.493 2,242.639 ↓ 151.5 17,727 1

Hash Semi Join (cost=428,039.33..455,966.22 rows=117 width=1,168) (actual time=1,277.044..2,242.639 rows=17,727 loops=1)

  • Hash Cond: (phistorico0_.id_registro = ANY_subquery.max)
7. 41.136 722.413 ↑ 1.0 75,735 1

Hash Join (cost=1,766.89..29,493.56 rows=75,777 width=1,168) (actual time=17.939..722.413 rows=75,735 loops=1)

  • Hash Cond: (phistorico0_.pos_id_via_hist = phvia6_.id_registro)
8. 40.812 681.027 ↑ 1.0 75,735 1

Hash Left Join (cost=1,741.24..28,425.98 rows=75,777 width=1,133) (actual time=17.680..681.027 rows=75,735 loops=1)

  • Hash Cond: (phistorico0_.pos_id_unidadpob_hist = phunidadpo5_.id_registro)
9. 36.408 640.088 ↑ 1.0 75,735 1

Hash Left Join (cost=1,731.81..27,374.61 rows=75,777 width=1,005) (actual time=17.547..640.088 rows=75,735 loops=1)

  • Hash Cond: (phistorico0_.pos_id_tramo_hist = phtramo4_.id_registro)
10. 24.554 603.227 ↑ 1.0 75,735 1

Hash Left Join (cost=1,671.51..26,272.38 rows=75,777 width=988) (actual time=17.089..603.227 rows=75,735 loops=1)

  • Hash Cond: (phistorico0_.pos_id_pseudovia_hist = phpseudovi3_.id_registro)
11. 46.593 578.672 ↑ 1.0 75,735 1

Hash Left Join (cost=1,654.31..25,971.00 rows=75,777 width=964) (actual time=17.081..578.672 rows=75,735 loops=1)

  • Hash Cond: (((phistorico0_.cd_prov_ent)::text = (pestudio2_.cd_prov_ent)::text) AND ((phistorico0_.cd_mun_ent)::text = (pestudio2_.cd_mun_ent)::text) AND ((phistorico0_.pos_cd_estdo)::text = (pestudio (...)
12. 430.782 532.065 ↑ 1.0 75,735 1

Hash Join (cost=1,653.00..25,077.33 rows=75,777 width=952) (actual time=17.050..532.065 rows=75,735 loops=1)

  • Hash Cond: (phistorico0_.pos_id_hoja_hist = phhojapadr1_.id_registro)
13. 85.137 85.137 ↑ 1.0 75,735 1

Seq Scan on phistorico phistorico0_ (cost=0.00..7,732.67 rows=75,777 width=726) (actual time=0.006..85.137 rows=75,735 loops=1)

  • Filter: ((tipo_mov <> 'B'::bpchar) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '035'::text))
  • Rows Removed by Filter: 18589
14. 9.031 16.146 ↓ 1.0 19,282 1

Hash (cost=770.99..770.99 rows=19,281 width=242) (actual time=16.146..16.146 rows=19,282 loops=1)

  • Buckets: 1024 Batches: 8 Memory Usage: 247kB
15. 5.793 7.115 ↓ 1.0 19,282 1

Hash Join (cost=1.07..770.99 rows=19,281 width=242) (actual time=0.010..7.115 rows=19,282 loops=1)

  • Hash Cond: (phhojapadr1_.cd_tipo_hoja = (ptipohoja8_.cd_tipo_hoja)::bpchar)
16. 1.319 1.319 ↓ 1.0 19,282 1

Seq Scan on phhojapadronal phhojapadr1_ (cost=0.00..504.81 rows=19,281 width=102) (actual time=0.002..1.319 rows=19,282 loops=1)

17. 0.002 0.003 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=140) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
18. 0.001 0.001 ↑ 1.0 3 1

Seq Scan on ptipohoja ptipohoja8_ (cost=0.00..1.03 rows=3 width=140) (actual time=0.001..0.001 rows=3 loops=1)

19. 0.005 0.014 ↓ 19.0 19 1

Hash (cost=1.28..1.28 rows=1 width=56) (actual time=0.014..0.014 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
20. 0.009 0.009 ↓ 19.0 19 1

Seq Scan on pestudio pestudio2_ (cost=0.00..1.28 rows=1 width=56) (actual time=0.005..0.009 rows=19 loops=1)

  • Filter: (((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '035'::text))
21. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=13.20..13.20 rows=320 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
22. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on phpseudovia phpseudovi3_ (cost=0.00..13.20 rows=320 width=32) (actual time=0.001..0.001 rows=0 loops=1)

23. 0.231 0.453 ↑ 1.0 1,480 1

Hash (cost=41.80..41.80 rows=1,480 width=25) (actual time=0.453..0.453 rows=1,480 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
24. 0.222 0.222 ↑ 1.0 1,480 1

Seq Scan on phtramo phtramo4_ (cost=0.00..41.80 rows=1,480 width=25) (actual time=0.003..0.222 rows=1,480 loops=1)

25. 0.083 0.127 ↑ 1.0 197 1

Hash (cost=6.97..6.97 rows=197 width=128) (actual time=0.127..0.127 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
26. 0.044 0.044 ↑ 1.0 197 1

Seq Scan on phunidadpob phunidadpo5_ (cost=0.00..6.97 rows=197 width=128) (actual time=0.003..0.044 rows=197 loops=1)

27. 0.151 0.250 ↑ 1.0 651 1

Hash (cost=17.51..17.51 rows=651 width=43) (actual time=0.250..0.250 rows=651 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
28. 0.099 0.099 ↑ 1.0 651 1

Seq Scan on phvia phvia6_ (cost=0.00..17.51 rows=651 width=43) (actual time=0.007..0.099 rows=651 loops=1)

29. 7.165 1,257.733 ↓ 226.6 33,087 1

Hash (cost=426,270.62..426,270.62 rows=146 width=8) (actual time=1,257.733..1,257.733 rows=33,087 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB
30. 2.170 1,250.568 ↓ 226.6 33,087 1

Subquery Scan on ANY_subquery (cost=426,267.70..426,270.62 rows=146 width=8) (actual time=1,239.774..1,250.568 rows=33,087 loops=1)

31. 38.931 1,248.398 ↓ 226.6 33,087 1

HashAggregate (cost=426,267.70..426,269.16 rows=146 width=19) (actual time=1,239.773..1,248.398 rows=33,087 loops=1)

32. 98.374 1,209.467 ↓ 73.1 33,561 1

Bitmap Heap Scan on phistorico phistorico9_ (cost=2,571.18..426,263.11 rows=459 width=19) (actual time=11.855..1,209.467 rows=33,561 loops=1)

  • Recheck Cond: ((fecha_real <= '2017-12-31 00:00:00'::timestamp without time zone) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '035'::text))
  • Filter: (fecha_real = (SubPlan 1))
  • Rows Removed by Filter: 58112
33. 11.017 11.017 ↑ 1.0 91,673 1

Bitmap Index Scan on idx_phco_fecha_real_prov_mun (cost=0.00..2,571.07 rows=91,732 width=0) (actual time=11.017..11.017 rows=91,673 loops=1)

  • Index Cond: ((fecha_real <= '2017-12-31 00:00:00'::timestamp without time zone) AND ((cd_prov_ent)::text = '36'::text) AND ((cd_mun_ent)::text = '035'::text))
34.          

SubPlan (forBitmap Heap Scan)

35. 91.673 1,100.076 ↑ 1.0 1 91,673

GroupAggregate (cost=0.42..4.53 rows=1 width=19) (actual time=0.012..0.012 rows=1 loops=91,673)

36. 1,008.403 1,008.403 ↓ 1.3 4 91,673

Index Only Scan using idx_phco_prov_mun_fecreal on phistorico phistorico10_ (cost=0.42..4.49 rows=3 width=19) (actual time=0.011..0.011 rows=4 loops=91,673)

  • Index Cond: ((cd_prov_ent = '36'::text) AND (cd_mun_ent = '035'::text) AND (id_habitante = phistorico9_.id_habitante) AND (fecha_real <= '2017-12-31 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0