explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aUxv

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 93,917.304 ↑ 8.0 25 1

Sort (cost=5,356,067.58..5,356,068.08 rows=200 width=24) (actual time=93,917.302..93,917.304 rows=25 loops=1)

  • Sort Key: grp.tgt_org_unit_id DESC
  • Sort Method: quicksort Memory: 26kB
2. 180.152 93,917.198 ↑ 8.0 25 1

HashAggregate (cost=5,356,057.94..5,356,059.94 rows=200 width=24) (actual time=93,917.192..93,917.198 rows=25 loops=1)

  • Group Key: grp.tgt_org_unit_id
3. 12,831.523 93,737.046 ↑ 3.4 808,848 1

Hash Join (cost=4,624,134.87..5,335,188.44 rows=2,782,600 width=24) (actual time=80,918.093..93,737.046 rows=808,848 loops=1)

  • Hash Cond: ((zc.zip_code)::text = vr.zip_code)
4. 0.171 0.537 ↑ 2.3 325 1

HashAggregate (cost=34.83..42.27 rows=744 width=14) (actual time=0.446..0.537 rows=325 loops=1)

  • Group Key: grp.tgt_org_unit_id, zc.zip_code
5.          

Initplan (for HashAggregate)

6. 0.003 0.015 ↑ 1.0 1 1

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

7. 0.012 0.012 ↑ 1.0 2 1

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

8. 0.058 0.351 ↑ 2.3 325 1

Hash Join (cost=14.62..30.08 rows=744 width=14) (actual time=0.281..0.351 rows=325 loops=1)

  • Hash Cond: ((oa.tgt_org_unit_id)::text = (zc.org_unit_id)::text)
9. 0.106 0.174 ↑ 3.5 25 1

Hash Join (cost=3.30..8.06 rows=87 width=24) (actual time=0.155..0.174 rows=25 loops=1)

  • Hash Cond: ((grp.src_org_unit_id)::text = (oa.tgt_org_unit_id)::text)
10. 0.025 0.025 ↓ 1.0 51 1

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

  • Filter: (tgt_org_level_number = 2)
  • Rows Removed by Filter: 26
11. 0.007 0.043 ↓ 1.9 25 1

Hash (cost=3.14..3.14 rows=13 width=8) (actual time=0.043..0.043 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.036 0.036 ↓ 1.9 25 1

Seq Scan on org_ancestors oa (cost=0.00..3.14 rows=13 width=8) (actual time=0.025..0.036 rows=25 loops=1)

  • Filter: ((tgt_org_level_number = $0) AND ((src_org_unit_id)::text = 'Organization-1'::text))
  • Rows Removed by Filter: 52
13. 0.048 0.119 ↓ 1.0 327 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
14. 0.071 0.071 ↓ 1.0 327 1

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

15. 29,330.127 80,904.986 ↓ 1.0 111,865,028 1

Hash (cost=2,570,327.24..2,570,327.24 rows=111,865,024 width=22) (actual time=80,904.986..80,904.986 rows=111,865,028 loops=1)

  • Buckets: 2,097,152 Batches: 64 Memory Usage: 113,450kB
16. 51,574.859 51,574.859 ↓ 1.0 111,865,028 1

Seq Scan on atdntd_reg_vehicle_exp vr (cost=0.00..2,570,327.24 rows=111,865,024 width=22) (actual time=2.417..51,574.859 rows=111,865,028 loops=1)

Planning time : 0.490 ms
Execution time : 93,918.042 ms