explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PhCq : Optimization for: JOIN Query; plan #kGP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.770 189.020 ↓ 51.5 10,300 1

HashAggregate (cost=30,740.57..30,742.57 rows=200 width=4) (actual time=187.783..189.020 rows=10,300 loops=1)

  • Output: zone_to_cloudfolder_edge.cloudfolderid
  • Group Key: zone_to_cloudfolder_edge.cloudfolderid
2.          

CTE zones

3. 0.191 0.540 ↑ 1.9 103 1

HashAggregate (cost=3,887.43..3,889.43 rows=200 width=4) (actual time=0.466..0.540 rows=103 loops=1)

  • Output: device_to_zone_edge.zoneid
  • Group Key: device_to_zone_edge.zoneid
4. 0.349 0.349 ↑ 53.4 103 1

Index Scan using deviceid_index on public.device_to_zone_edge (cost=0.43..3,873.68 rows=5,500 width=4) (actual time=0.042..0.349 rows=103 loops=1)

  • Output: device_to_zone_edge.deviceid, device_to_zone_edge.zoneid, device_to_zone_edge.version
  • Index Cond: (device_to_zone_edge.deviceid = 1,234)
5. 99.472 184.250 ↑ 53.5 10,300 1

Hash Join (cost=9.00..25,472.61 rows=551,412 width=4) (actual time=0.984..184.250 rows=10,300 loops=1)

  • Output: zone_to_cloudfolder_edge.cloudfolderid
  • Inner Unique: true
  • Hash Cond: (zone_to_cloudfolder_edge.zoneid = zones.zoneid)
6. 83.883 83.883 ↑ 1.1 1,000,000 1

Seq Scan on public.zone_to_cloudfolder_edge (cost=0.00..16,434.24 rows=1,102,824 width=8) (actual time=0.035..83.883 rows=1,000,000 loops=1)

  • Output: zone_to_cloudfolder_edge.zoneid, zone_to_cloudfolder_edge.cloudfolderid, zone_to_cloudfolder_edge.version
7. 0.073 0.895 ↑ 1.9 103 1

Hash (cost=6.50..6.50 rows=200 width=4) (actual time=0.895..0.895 rows=103 loops=1)

  • Output: zones.zoneid
  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
8. 0.176 0.822 ↑ 1.9 103 1

HashAggregate (cost=4.50..6.50 rows=200 width=4) (actual time=0.747..0.822 rows=103 loops=1)

  • Output: zones.zoneid
  • Group Key: zones.zoneid
9. 0.646 0.646 ↑ 1.9 103 1

CTE Scan on zones (cost=0.00..4.00 rows=200 width=4) (actual time=0.471..0.646 rows=103 loops=1)

  • Output: zones.zoneid