explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mMNh : CTE with re-use

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.009 38.516 ↑ 200.0 1 1

HashAggregate (cost=1,096.39..1,098.39 rows=200 width=4) (actual time=38.515..38.516 rows=1 loops=1)

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

CTE allcloudfolders

3. 6.713 16.263 ↓ 1.3 10,604 1

Nested Loop (cost=0.85..408.01 rows=7,870 width=16) (actual time=0.105..16.263 rows=10,604 loops=1)

  • Output: dtz.version, ztcf.zoneid, ztcf.cloudfolderid, ztcf.version
4. 0.540 0.540 ↓ 1.3 106 1

Index Scan using deviceid_index on public.device_to_zone_edge dtz (cost=0.43..78.18 rows=79 width=8) (actual time=0.048..0.540 rows=106 loops=1)

  • Output: dtz.deviceid, dtz.zoneid, dtz.version
  • Index Cond: (dtz.deviceid = 1,234)
5. 9.010 9.010 ↑ 1.0 100 106

Index Only Scan using zone_to_cloudfolder_edge_index1 on public.zone_to_cloudfolder_edge ztcf (cost=0.42..3.17 rows=100 width=12) (actual time=0.028..0.085 rows=100 loops=106)

  • Output: ztcf.zoneid, ztcf.version, ztcf.cloudfolderid
  • Index Cond: (ztcf.zoneid = dtz.zoneid)
  • Heap Fetches: 5
6.          

CTE newcloudfolders

7. 0.015 33.676 ↑ 200.0 1 1

HashAggregate (cost=207.68..209.68 rows=200 width=4) (actual time=33.673..33.676 rows=1 loops=1)

  • Output: allcloudfolders_1.cloudfolderid
  • Group Key: allcloudfolders_1.cloudfolderid
8. 33.661 33.661 ↑ 4,372.0 1 1

CTE Scan on allcloudfolders allcloudfolders_1 (cost=0.00..196.75 rows=4,372 width=4) (actual time=0.461..33.661 rows=1 loops=1)

  • Output: allcloudfolders_1.deviceversion, allcloudfolders_1.zoneid, allcloudfolders_1.cloudfolderid, allcloudfolders_1.zoneversion
  • Filter: ((allcloudfolders_1.deviceversion > 2100012) OR (allcloudfolders_1.zoneversion > 2100012))
  • Rows Removed by Filter: 10,603
9. 2.643 38.507 ↑ 7,870.0 1 1

Hash Right Join (cost=6.50..459.03 rows=7,870 width=4) (actual time=33.762..38.507 rows=1 loops=1)

  • Output: newcf.cloudfolderid
  • Hash Cond: (allcloudfolders.cloudfolderid = newcf.cloudfolderid)
10. 2.172 2.172 ↓ 1.3 10,604 1

CTE Scan on allcloudfolders (cost=0.00..157.40 rows=7,870 width=4) (actual time=0.001..2.172 rows=10,604 loops=1)

  • Output: allcloudfolders.deviceversion, allcloudfolders.zoneid, allcloudfolders.cloudfolderid, allcloudfolders.zoneversion
11. 0.011 33.692 ↑ 200.0 1 1

Hash (cost=4.00..4.00 rows=200 width=4) (actual time=33.692..33.692 rows=1 loops=1)

  • Output: newcf.cloudfolderid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 33.681 33.681 ↑ 200.0 1 1

CTE Scan on newcloudfolders newcf (cost=0.00..4.00 rows=200 width=4) (actual time=33.677..33.681 rows=1 loops=1)

  • Output: newcf.cloudfolderid