explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sox4

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 1,777.467 ↑ 1.0 25 1

Sort (cost=1,307,868.50..1,307,868.56 rows=25 width=24) (actual time=1,777.466..1,777.467 rows=25 loops=1)

  • Sort Key: grp.tgt_org_unit_id DESC
  • Sort Method: quicksort Memory: 26kB
2.          

Initplan (for Sort)

3. 0.004 0.010 ↑ 1.0 1 1

Aggregate (cost=1.02..1.03 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

4. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on org_level (cost=0.00..1.02 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=1)

5. 174.049 1,777.420 ↑ 1.0 25 1

HashAggregate (cost=1,307,866.64..1,307,866.89 rows=25 width=24) (actual time=1,777.414..1,777.420 rows=25 loops=1)

  • Group Key: grp.tgt_org_unit_id
6. 194.912 1,603.371 ↑ 3.4 808,848 1

Hash Join (cost=87.19..1,287,017.29 rows=2,779,913 width=24) (actual time=1.092..1,603.371 rows=808,848 loops=1)

  • Hash Cond: ((oa.tgt_org_unit_id)::text = (grp.src_org_unit_id)::text)
7. 146.650 1,408.424 ↓ 2.0 808,848 1

Nested Loop (cost=84.62..1,244,638.34 rows=414,745 width=28) (actual time=1.050..1,408.424 rows=808,848 loops=1)

8. 0.236 0.449 ↓ 2.9 325 1

Hash Join (cost=11.31..15.05 rows=111 width=18) (actual time=0.123..0.449 rows=325 loops=1)

  • Hash Cond: ((oa.tgt_org_unit_id)::text = (zc.org_unit_id)::text)
9. 0.109 0.109 ↓ 1.9 25 1

Seq Scan on org_ancestors oa (cost=0.00..2.14 rows=13 width=8) (actual time=0.014..0.109 rows=25 loops=1)

  • Filter: ((tgt_org_level_number = $0) AND ((src_org_unit_id)::text = 'Organization-1'::text))
  • Rows Removed by Filter: 51
10. 0.045 0.104 ↓ 1.0 327 1

Hash (cost=7.25..7.25 rows=325 width=10) (actual time=0.104..0.104 rows=327 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
11. 0.059 0.059 ↓ 1.0 327 1

Seq Scan on market_zip_codes zc (cost=0.00..7.25 rows=325 width=10) (actual time=0.007..0.059 rows=327 loops=1)

12. 1,155.375 1,261.325 ↑ 1.5 2,489 325

Bitmap Heap Scan on atdntd_reg_vehicle_exp vr (cost=73.31..11,175.42 rows=3,740 width=22) (actual time=0.683..3.881 rows=2,489 loops=325)

  • Recheck Cond: (zip_code = (zc.zip_code)::text)
  • Heap Blocks: exact=807865
13. 105.950 105.950 ↑ 1.5 2,489 325

Bitmap Index Scan on atdntd_reg_vehicle_exp_zip_code_idx (cost=0.00..72.37 rows=3,740 width=0) (actual time=0.326..0.326 rows=2,489 loops=325)

  • Index Cond: (zip_code = (zc.zip_code)::text)
14. 0.011 0.035 ↑ 1.0 50 1

Hash (cost=1.95..1.95 rows=50 width=16) (actual time=0.034..0.035 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.024 0.024 ↑ 1.0 50 1

Seq Scan on org_ancestors grp (cost=0.00..1.95 rows=50 width=16) (actual time=0.013..0.024 rows=50 loops=1)

  • Filter: (tgt_org_level_number = 2)
  • Rows Removed by Filter: 26
Planning time : 0.645 ms
Execution time : 1,777.571 ms