explain.depesz.com

PostgreSQL's explain analyze made readable

Result: peWg

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 75,088.023 ↑ 8.0 25 1

Sort (cost=5,356,065.58..5,356,066.08 rows=200 width=24) (actual time=75,088.022..75,088.023 rows=25 loops=1)

  • Sort Key: grp.tgt_org_unit_id DESC
  • Sort Method: quicksort Memory: 26kB
2. 164.358 75,087.986 ↑ 8.0 25 1

HashAggregate (cost=5,356,055.94..5,356,057.94 rows=200 width=24) (actual time=75,087.980..75,087.986 rows=25 loops=1)

  • Group Key: grp.tgt_org_unit_id
3. 10,566.124 74,923.628 ↑ 3.4 808,848 1

Hash Join (cost=4,624,132.87..5,335,186.44 rows=2,782,600 width=24) (actual time=64,380.659..74,923.628 rows=808,848 loops=1)

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

HashAggregate (cost=32.83..40.27 rows=744 width=14) (actual time=0.390..0.485 rows=325 loops=1)

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

Initplan (for HashAggregate)

6. 0.008 0.020 ↑ 1.0 1 1

Aggregate (cost=1.02..1.03 rows=1 width=8) (actual time=0.015..0.020 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.074 0.314 ↑ 2.3 325 1

Hash Join (cost=13.62..28.08 rows=744 width=14) (actual time=0.224..0.314 rows=325 loops=1)

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

Hash Join (cost=2.30..6.06 rows=87 width=24) (actual time=0.114..0.136 rows=25 loops=1)

  • Hash Cond: ((grp.src_org_unit_id)::text = (oa.tgt_org_unit_id)::text)
10. 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.012..0.024 rows=50 loops=1)

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

Hash (cost=2.14..2.14 rows=13 width=8) (actual time=0.042..0.042 rows=25 loops=1)

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

Seq Scan on org_ancestors oa (cost=0.00..2.14 rows=13 width=8) (actual time=0.027..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: 51
13. 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
14. 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)

15. 28,631.999 64,357.019 ↓ 1.0 111,865,028 1

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

  • Buckets: 2097152 Batches: 64 Memory Usage: 113450kB
16. 35,725.020 35,725.020 ↓ 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=0.154..35,725.020 rows=111,865,028 loops=1)

Planning time : 0.454 ms
Execution time : 75,088.740 ms