explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PpSc

Settings
# exclusive inclusive rows x rows loops node
1. 167.811 1,902.101 ↑ 1.0 25 1

HashAggregate (cost=1,307,867.67..1,307,867.92 rows=25 width=24) (actual time=1,902.096..1,902.101 rows=25 loops=1)

  • Group Key: grp.tgt_org_unit_id
2.          

Initplan (for HashAggregate)

3. 0.004 0.010 ↑ 1.0 1 1

Aggregate (cost=1.02..1.03 rows=1 width=8) (actual time=0.010..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.006..0.006 rows=2 loops=1)

5. 187.477 1,734.280 ↑ 3.4 808,848 1

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

  • Hash Cond: ((oa.tgt_org_unit_id)::text = (grp.src_org_unit_id)::text)
6. 159.114 1,546.767 ↓ 2.0 808,848 1

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

7. 0.345 0.553 ↓ 2.9 325 1

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

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

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

  • Filter: ((tgt_org_level_number = $0) AND ((src_org_unit_id)::text = 'Organization-1'::text))
  • Rows Removed by Filter: 51
9. 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: 1,024 Batches: 1 Memory Usage: 22kB
10. 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.008..0.059 rows=327 loops=1)

11. 1,252.225 1,387.100 ↑ 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.772..4.268 rows=2,489 loops=325)

  • Recheck Cond: (zip_code = (zc.zip_code)::text)
  • Heap Blocks: exact=807,865
12. 134.875 134.875 ↑ 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.415..0.415 rows=2,489 loops=325)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 0.025 0.025 ↑ 1.0 50 1

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

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