explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1o7m

Settings
# exclusive inclusive rows x rows loops node
1. 0.165 10.024 ↓ 97.0 194 1

Unique (cost=578.10..578.23 rows=2 width=445) (actual time=9.819..10.024 rows=194 loops=1)

2.          

CTE data1

3. 0.148 0.557 ↑ 1.1 168 1

Hash Join (cost=26.07..31.95 rows=186 width=467) (actual time=0.358..0.557 rows=168 loops=1)

  • Hash Cond: (uiv.image_id = image.id)
4. 0.063 0.063 ↓ 1.0 190 1

Seq Scan on user_image_version uiv (cost=0.00..5.38 rows=186 width=24) (actual time=0.006..0.063 rows=190 loops=1)

  • Filter: (user_id = 218)
  • Rows Removed by Filter: 80
5. 0.178 0.346 ↑ 1.0 270 1

Hash (cost=22.70..22.70 rows=270 width=451) (actual time=0.346..0.346 rows=270 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 111kB
6. 0.168 0.168 ↑ 1.0 270 1

Seq Scan on image (cost=0.00..22.70 rows=270 width=451) (actual time=0.004..0.168 rows=270 loops=1)

7.          

CTE data2

8. 0.047 0.474 ↓ 30.0 30 1

Nested Loop (cost=1.26..536.30 rows=1 width=459) (actual time=0.060..0.474 rows=30 loops=1)

9. 0.073 0.155 ↓ 1.1 34 1

Hash Join (cost=1.11..6.85 rows=30 width=16) (actual time=0.032..0.155 rows=34 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
10. 0.075 0.075 ↑ 1.0 270 1

Seq Scan on user_image_version uiv_1 (cost=0.00..4.70 rows=270 width=16) (actual time=0.002..0.075 rows=270 loops=1)

  • Filter: (deleted IS NULL)
11. 0.002 0.007 ↓ 3.0 3 1

Hash (cost=1.10..1.10 rows=1 width=16) (actual time=0.007..0.007 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.005 0.005 ↓ 3.0 3 1

Seq Scan on family_friend (cost=0.00..1.10 rows=1 width=16) (actual time=0.003..0.005 rows=3 loops=1)

  • Filter: (family_member_id = 218)
  • Rows Removed by Filter: 5
13. 0.074 0.272 ↑ 1.0 1 34

Index Scan using image_pk on image image_1 (cost=0.15..17.08 rows=1 width=451) (actual time=0.008..0.008 rows=1 loops=34)

  • Index Cond: (id = uiv_1.image_id)
  • Filter: ((SubPlan 2) = version_date)
  • Rows Removed by Filter: 0
14.          

SubPlan (for Index Scan)

15. 0.033 0.198 ↑ 1.0 1 33

Aggregate (cost=16.58..16.59 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=33)

16. 0.066 0.165 ↑ 3.0 1 33

Bitmap Heap Scan on image iimage (cost=8.02..16.57 rows=3 width=8) (actual time=0.004..0.005 rows=1 loops=33)

  • Recheck Cond: (version_history && image_1.version_history)
  • Heap Blocks: exact=45
17. 0.099 0.099 ↑ 3.0 1 33

Bitmap Index Scan on idx_image_version_history (cost=0.00..8.02 rows=3 width=0) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: (version_history && image_1.version_history)
18. 0.707 9.859 ↓ 97.0 194 1

Sort (cost=9.85..9.86 rows=2 width=445) (actual time=9.818..9.859 rows=194 loops=1)

  • Sort Key: data1.object_key, data1.system, data1.thumbnail, data1.image_type, data1.id, data1.description, data1.exif_date_time, data1.exif_lat_long, data1.exif_orientation, data1.location_id, data1.bucket, data1.has_people, data1.file_size, data1.author_id, data1.date_id, data1.upload_date, data1.metadata, data1.previous_version, data1.version_history, data1.version_date, data1.hash, data1.user_id, data1.uploaded_by, (CASE WHEN (data2.version_date > data1.version_date) THEN data2.id ELSE NULL::bigint END)
  • Sort Method: quicksort Memory: 125kB
19. 0.074 9.152 ↓ 97.0 194 1

Append (cost=0.00..9.84 rows=2 width=445) (actual time=0.908..9.152 rows=194 loops=1)

20. 2.493 5.280 ↓ 168.0 168 1

Nested Loop Left Join (cost=0.00..3.76 rows=1 width=445) (actual time=0.908..5.280 rows=168 loops=1)

  • Join Filter: (data1.version_history && data2.version_history)
  • Rows Removed by Join Filter: 5036
21. 0.771 0.771 ↓ 168.0 168 1

CTE Scan on data1 (cost=0.00..3.72 rows=1 width=437) (actual time=0.362..0.771 rows=168 loops=1)

  • Filter: (deleted IS NULL)
22. 2.016 2.016 ↓ 30.0 30 168

CTE Scan on data2 (cost=0.00..0.02 rows=1 width=48) (actual time=0.001..0.012 rows=30 loops=168)

23. 2.409 3.798 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.00..6.07 rows=1 width=445) (actual time=0.114..3.798 rows=26 loops=1)

  • Join Filter: (data1_1.version_history && data2_1.version_history)
  • Rows Removed by Join Filter: 5036
  • Filter: (data1_1.id IS NULL)
  • Rows Removed by Filter: 4
24. 0.009 0.009 ↓ 30.0 30 1

CTE Scan on data2 data2_1 (cost=0.00..0.02 rows=1 width=437) (actual time=0.000..0.009 rows=30 loops=1)

25. 1.380 1.380 ↑ 1.1 168 30

CTE Scan on data1 data1_1 (cost=0.00..3.72 rows=186 width=40) (actual time=0.000..0.046 rows=168 loops=30)

Planning time : 0.711 ms
Execution time : 10.214 ms