explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8V1V : Optimization for: plan #ecLu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.010 10,448.219 ↑ 292.1 31 1

Unique (cost=786,991.67..787,059.58 rows=9,055 width=27) (actual time=10,448.210..10,448.219 rows=31 loops=1)

2. 0.076 10,448.209 ↑ 292.1 31 1

Sort (cost=786,991.67..787,014.31 rows=9,055 width=27) (actual time=10,448.208..10,448.209 rows=31 loops=1)

  • Sort Key: tid1.tiddsc, tid1.tidid
  • Sort Method: quicksort Memory: 27kB
3. 8.072 10,448.133 ↑ 292.1 31 1

HashAggregate (cost=786,306.00..786,396.55 rows=9,055 width=27) (actual time=10,448.048..10,448.133 rows=31 loops=1)

  • Group Key: tid1.tiddsc, tid1.tidid
4. 1,874.894 10,440.061 ↓ 1.5 21,731 1

Hash Join (cost=81,423.66..786,231.71 rows=14,858 width=27) (actual time=1,683.214..10,440.061 rows=21,731 loops=1)

  • Hash Cond: ((ent.co_entidade)::bigint = (dsh.dshcod)::bigint)
5. 7,820.331 8,555.380 ↑ 1.0 4,328,662 1

Bitmap Heap Scan on ts_censo_basico_docente ent (cost=81,354.39..747,917.13 rows=4,346,059 width=9) (actual time=902.756..8,555.380 rows=4,328,662 loops=1)

  • Recheck Cond: (tp_dependencia = 3)
  • Rows Removed by Index Recheck: 2,334,795
  • Heap Blocks: exact=335,275 lossy=216,594
6. 735.049 735.049 ↑ 1.0 4,328,662 1

Bitmap Index Scan on idx_ts_censo_basico_docente_tp_dependencia (cost=0.00..80,267.88 rows=4,346,059 width=0) (actual time=735.049..735.049 rows=4,328,662 loops=1)

  • Index Cond: (tp_dependencia = 3)
7. 0.481 9.787 ↓ 651.0 651 1

Hash (cost=69.26..69.26 rows=1 width=35) (actual time=9.787..9.787 rows=651 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
8. 0.667 9.306 ↓ 651.0 651 1

Nested Loop (cost=2.13..69.26 rows=1 width=35) (actual time=0.106..9.306 rows=651 loops=1)

9. 1.254 4.733 ↓ 651.0 651 1

Nested Loop (cost=1.71..68.34 rows=1 width=35) (actual time=0.079..4.733 rows=651 loops=1)

  • -> Index Only Scan using ix_detalheperiodicidade_dpeid on detalheperiodicidade dpe (cost=0.28..4.30 rows=1 width=4) (actualtime=0.001..0.001 rows=1 loops=651)
10. 0.137 3.479 ↓ 651.0 651 1

Nested Loop (cost=1.43..64.03 rows=1 width=39) (actual time=0.071..3.479 rows=651 loops=1)

  • Index Cond: (dpeid = sh.dpeid)
  • Heap Fetches: 0
11. 0.072 2.040 ↓ 651.0 651 1

Nested Loop (cost=1.14..63.68 rows=1 width=16) (actual time=0.066..2.040 rows=651 loops=1)

12. 0.209 0.666 ↓ 325.5 651 1

Nested Loop (cost=0.85..63.03 rows=2 width=20) (actual time=0.056..0.666 rows=651 loops=1)

13. 0.014 0.014 ↑ 4.0 1 1

Index Scan using ix_seriehistorica_indid on seriehistorica sh (cost=0.29..18.15 rows=4 width=8) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (indid = 3,207)
  • Filter: (sehstatus <> 'I'::bpchar)
14. 0.443 0.443 ↓ 325.5 651 1

Index Scan using ix_detalheserirhistorica_sehiddshcodmunicipio on detalheseriehistorica dsh (cost=0.57..11.20 rows=2 width=20) (actual time=0.040..0.443 rows=651 loops=1)

  • Index Cond: ((sehid = sh.sehid) AND (dshcodmunicipio = '2602001'::bpchar))
15. 1.302 1.302 ↑ 1.0 1 651

Index Only Scan using detalhetipodadosindicador_pkey on detalhetipodadosindicador tid2 (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=651)

  • Index Cond: (tidid = dsh.tidid2)
  • Heap Fetches: 0
16. 1.302 1.302 ↑ 1.0 1 651

Index Scan using detalhetipodadosindicador_pkey on detalhetipodadosindicador tid1 (cost=0.29..0.34 rows=1 width=27) (actual time=0.001..0.002 rows=1 loops=651)

  • Index Cond: (tidid = dsh.tidid1)
17. 3.906 3.906 ↑ 1.0 1 651

Index Only Scan using escola_pkey on escola esc (cost=0.42..0.91 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=651)

  • Index Cond: (esccodinep = (dsh.dshcod)::text)
  • Heap Fetches: 0
Planning time : 20.322 ms
Execution time : 10,448.554 ms