explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3h7D

Settings
# exclusive inclusive rows x rows loops node
1. 174.382 1,749.800 ↑ 1.0 25 1

HashAggregate (cost=1,305,203.03..1,305,203.28 rows=25 width=24) (actual time=1,749.795..1,749.800 rows=25 loops=1)

  • Group Key: grp.tgt_org_unit_id
2.          

Initplan (for HashAggregate)

3. 0.004 0.009 ↑ 1.0 1 1

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

4. 0.005 0.005 ↑ 1.0 2 1

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

5. 186.525 1,575.409 ↑ 3.4 808,848 1

Hash Join (cost=87.13..1,284,387.83 rows=2,775,222 width=24) (actual time=1.363..1,575.409 rows=808,848 loops=1)

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

Nested Loop (cost=84.56..1,242,080.41 rows=414,045 width=28) (actual time=1.320..1,388.848 rows=808,848 loops=1)

7. 0.284 0.488 ↓ 2.9 325 1

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

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

Seq Scan on org_ancestors oa (cost=0.00..2.14 rows=13 width=8) (actual time=0.013..0.102 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.102 ↓ 1.0 327 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
10. 0.057 0.057 ↓ 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.057 rows=327 loops=1)

11. 1,133.925 1,241.825 ↑ 1.5 2,489 325

Bitmap Heap Scan on atdntd_reg_vehicle_exp vr (cost=73.24..11,152.46 rows=3,732 width=22) (actual time=0.688..3.821 rows=2,489 loops=325)

  • Recheck Cond: (zip_code = (zc.zip_code)::text)
  • Heap Blocks: exact=807,865
12. 107.900 107.900 ↑ 1.5 2,489 325

Bitmap Index Scan on atdntd_reg_vehicle_exp_zip_code_idx (cost=0.00..72.31 rows=3,732 width=0) (actual time=0.332..0.332 rows=2,489 loops=325)

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

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

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

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

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