explain.depesz.com

PostgreSQL's explain analyze made readable

Result: clsO

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 1,247.825 ↑ 1.9 72 1

Sort (cost=7,138.90..7,139.25 rows=139 width=527) (actual time=1,247.817..1,247.825 rows=72 loops=1)

  • Sort Key: query_part.upload_date DESC
  • Sort Method: quicksort Memory: 62kB
2.          

CTE query_part

3. 0.014 1,192.670 ↓ 25.0 50 1

Limit (cost=6,826.30..6,826.38 rows=2 width=308) (actual time=1,192.607..1,192.670 rows=50 loops=1)

4. 0.047 1,192.656 ↓ 25.0 50 1

Unique (cost=6,826.30..6,826.38 rows=2 width=308) (actual time=1,192.606..1,192.656 rows=50 loops=1)

5. 0.636 1,192.609 ↓ 25.0 50 1

Sort (cost=6,826.30..6,826.31 rows=2 width=308) (actual time=1,192.605..1,192.609 rows=50 loops=1)

  • Sort Key: image_2.upload_date DESC, image_2.object_key, image_2.system, image_2.thumbnail, image_2.image_type, image_2.id, image_2.description, image_2.exif_orientation, image_2.location_id, image_2.author_id, image_2.exif_lat_long, image_2.date_id, image_2.bucket, image_2.metadata, image_2.uploaded_by
  • Sort Method: quicksort Memory: 463kB
6. 0.289 1,191.973 ↓ 448.5 897 1

Subquery Scan on image_2 (cost=6,826.15..6,826.29 rows=2 width=308) (actual time=1,191.168..1,191.973 rows=897 loops=1)

7. 0.444 1,191.684 ↓ 448.5 897 1

Unique (cost=6,826.15..6,826.27 rows=2 width=437) (actual time=1,191.166..1,191.684 rows=897 loops=1)

8.          

CTE data1

9. 0.451 1.592 ↓ 1.2 936 1

Hash Join (cost=102.44..127.20 rows=772 width=493) (actual time=1.048..1.592 rows=936 loops=1)

  • Hash Cond: (uiv.image_id = image.id)
10. 0.103 0.103 ↓ 1.2 936 1

Seq Scan on user_image_version uiv (cost=0.00..22.72 rows=772 width=24) (actual time=0.004..0.103 rows=936 loops=1)

11. 0.532 1.038 ↑ 1.0 953 1

Hash (cost=90.53..90.53 rows=953 width=477) (actual time=1.038..1.038 rows=953 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 394kB
12. 0.506 0.506 ↑ 1.0 953 1

Seq Scan on image (cost=0.00..90.53 rows=953 width=477) (actual time=0.002..0.506 rows=953 loops=1)

13.          

CTE data2

14. 835.214 1,179.331 ↓ 2,802.0 2,802 1

Nested Loop Anti Join (cost=1.32..6,679.60 rows=1 width=485) (actual time=0.511..1,179.331 rows=2,802 loops=1)

  • Join Filter: ((image_1.version_history && data1.version_history) AND (data1.author_id = family_friend.family_member_id))
  • Rows Removed by Join Filter: 2623373
15. 3.269 24.461 ↓ 2,804.0 2,804 1

Nested Loop (cost=1.32..6,652.58 rows=1 width=485) (actual time=0.051..24.461 rows=2,804 loops=1)

16. 1.092 1.424 ↓ 16.4 2,824 1

Hash Join (cost=1.04..28.38 rows=172 width=16) (actual time=0.020..1.424 rows=2,824 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
17. 0.325 0.325 ↓ 1.2 936 1

Seq Scan on user_image_version uiv_1 (cost=0.00..22.72 rows=772 width=16) (actual time=0.004..0.325 rows=936 loops=1)

  • Filter: (deleted IS NULL)
18. 0.004 0.007 ↓ 6.0 12 1

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.007..0.007 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.003 0.003 ↓ 6.0 12 1

Seq Scan on family_friend (cost=0.00..1.02 rows=2 width=16) (actual time=0.001..0.003 rows=12 loops=1)

20. 8.472 19.768 ↑ 1.0 1 2,824

Index Scan using image_pk on image image_1 (cost=0.28..38.44 rows=1 width=477) (actual time=0.007..0.007 rows=1 loops=2,824)

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

SubPlan (for Index Scan)

22. 2.824 11.296 ↑ 1.0 1 2,824

Aggregate (cost=37.68..37.69 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,824)

23. 2.824 8.472 ↑ 10.0 1 2,824

Bitmap Heap Scan on image iimage (cost=8.07..37.66 rows=10 width=8) (actual time=0.003..0.003 rows=1 loops=2,824)

  • Recheck Cond: (version_history && image_1.version_history)
  • Heap Blocks: exact=2927
24. 5.648 5.648 ↑ 10.0 1 2,824

Bitmap Index Scan on idx_image_version_history (cost=0.00..8.07 rows=10 width=0) (actual time=0.002..0.002 rows=1 loops=2,824)

  • Index Cond: (version_history && image_1.version_history)
25. 319.656 319.656 ↓ 1.2 936 2,804

CTE Scan on data1 (cost=0.00..15.44 rows=772 width=40) (actual time=0.000..0.114 rows=936 loops=2,804)

26. 4.208 1,191.240 ↓ 448.5 897 1

Sort (cost=19.36..19.36 rows=2 width=437) (actual time=1,191.165..1,191.240 rows=897 loops=1)

  • Sort Key: data1_1.object_key, data1_1.system, data1_1.thumbnail, data1_1.image_type, data1_1.id, data1_1.description, data1_1.exif_date_time, data1_1.exif_lat_long, data1_1.exif_orientation, data1_1.location_id, data1_1.bucket, data1_1.has_people, data1_1.file_size, data1_1.author_id, data1_1.date_id, data1_1.upload_date, data1_1.metadata, data1_1.previous_version, data1_1.version_history, data1_1.version_date, data1_1.hash, data1_1.user_id, data1_1.uploaded_by
  • Sort Method: quicksort Memory: 488kB
27. 0.154 1,187.032 ↓ 448.5 897 1

Append (cost=0.00..19.35 rows=2 width=437) (actual time=1.053..1,187.032 rows=897 loops=1)

28. 2.781 2.781 ↓ 119.0 119 1

CTE Scan on data1 data1_1 (cost=0.00..19.30 rows=1 width=437) (actual time=1.052..2.781 rows=119 loops=1)

  • Filter: ((deleted IS NULL) AND (image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 817
29. 1,184.097 1,184.097 ↓ 778.0 778 1

CTE Scan on data2 (cost=0.00..0.03 rows=1 width=437) (actual time=4.476..1,184.097 rows=778 loops=1)

  • Filter: ((image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 2024
30. 0.054 1,247.724 ↑ 1.9 72 1

Hash Left Join (cost=153.29..307.57 rows=139 width=527) (actual time=1,199.563..1,247.724 rows=72 loops=1)

  • Hash Cond: (query_part.id = image_event.image_id)
31. 0.037 1,243.399 ↓ 2.9 72 1

Hash Left Join (cost=112.64..262.38 rows=25 width=503) (actual time=1,195.287..1,243.399 rows=72 loops=1)

  • Hash Cond: (query_part.date_id = dates.id)
32. 0.080 1,243.243 ↓ 2.9 72 1

Nested Loop Left Join (cost=102.10..251.49 rows=25 width=493) (actual time=1,195.165..1,243.243 rows=72 loops=1)

  • Filter: (upvf.deleted IS NULL)
33. 0.313 1,194.131 ↓ 14.4 72 1

Hash Right Join (cost=101.85..151.24 rows=5 width=534) (actual time=1,193.663..1,194.131 rows=72 loops=1)

  • Hash Cond: (person_face.face_id = f.id)
34. 0.237 0.237 ↓ 1.2 1,955 1

Seq Scan on person_face (cost=0.00..43.25 rows=1,625 width=57) (actual time=0.003..0.237 rows=1,955 loops=1)

35. 0.052 1,193.581 ↓ 14.4 72 1

Hash (cost=101.78..101.78 rows=5 width=485) (actual time=1,193.581..1,193.581 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
36. 0.031 1,193.529 ↓ 14.4 72 1

Nested Loop Left Join (cost=11.92..101.78 rows=5 width=485) (actual time=1,193.317..1,193.529 rows=72 loops=1)

37. 0.267 1,193.354 ↓ 14.4 72 1

Hash Right Join (cost=11.64..99.71 rows=5 width=425) (actual time=1,193.305..1,193.354 rows=72 loops=1)

  • Hash Cond: (ifv.image_id = query_part.id)
38. 0.221 0.221 ↑ 1.4 2,060 1

Seq Scan on image_face_version ifv (cost=0.00..76.83 rows=2,983 width=16) (actual time=0.004..0.221 rows=2,060 loops=1)

39. 0.030 1,192.866 ↓ 25.0 50 1

Hash (cost=11.62..11.62 rows=2 width=417) (actual time=1,192.866..1,192.866 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
40. 0.027 1,192.836 ↓ 25.0 50 1

Hash Join (cost=5.24..11.62 rows=2 width=417) (actual time=1,192.813..1,192.836 rows=50 loops=1)

  • Hash Cond: (users.user_id = query_part.uploaded_by)
41. 0.018 0.018 ↓ 1.1 28 1

Seq Scan on users (cost=0.00..6.26 rows=26 width=29) (actual time=0.004..0.018 rows=28 loops=1)

42. 0.025 1,192.791 ↓ 25.0 50 1

Hash (cost=5.21..5.21 rows=2 width=396) (actual time=1,192.791..1,192.791 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
43. 0.037 1,192.766 ↓ 25.0 50 1

Hash Right Join (cost=0.07..5.21 rows=2 width=396) (actual time=1,192.752..1,192.766 rows=50 loops=1)

  • Hash Cond: (location.id = query_part.location_id)
44. 0.017 0.017 ↓ 1.2 97 1

Seq Scan on location (cost=0.00..4.82 rows=82 width=96) (actual time=0.004..0.017 rows=97 loops=1)

45. 0.015 1,192.712 ↓ 25.0 50 1

Hash (cost=0.04..0.04 rows=2 width=308) (actual time=1,192.712..1,192.712 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
46. 1,192.697 1,192.697 ↓ 25.0 50 1

CTE Scan on query_part (cost=0.00..0.04 rows=2 width=308) (actual time=1,192.608..1,192.697 rows=50 loops=1)

47. 0.144 0.144 ↑ 1.0 1 72

Index Scan using face_id_uindex on face f (cost=0.28..0.42 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=72)

  • Index Cond: (id = ifv.face_id)
48. 49.032 49.032 ↑ 1,000.0 1 72

Function Scan on user_person_version_set upvf (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.681..0.681 rows=1 loops=72)

49. 0.065 0.119 ↓ 1.2 336 1

Hash (cost=6.91..6.91 rows=291 width=18) (actual time=0.119..0.119 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
50. 0.054 0.054 ↓ 1.2 336 1

Seq Scan on dates (cost=0.00..6.91 rows=291 width=18) (actual time=0.005..0.054 rows=336 loops=1)

51. 0.019 4.271 ↑ 4.3 79 1

Hash (cost=36.42..36.42 rows=338 width=48) (actual time=4.271..4.271 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
52. 0.082 4.252 ↑ 4.3 79 1

Hash Join (cost=22.69..36.42 rows=338 width=48) (actual time=4.165..4.252 rows=79 loops=1)

  • Hash Cond: (image_event.event_id = uevv.id)
53. 0.011 0.011 ↓ 1.3 86 1

Seq Scan on image_event (cost=0.00..1.68 rows=68 width=16) (actual time=0.003..0.011 rows=86 loops=1)

54. 0.038 4.159 ↑ 6.2 161 1

Hash (cost=10.25..10.25 rows=995 width=40) (actual time=4.159..4.159 rows=161 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
55. 4.121 4.121 ↑ 6.2 161 1

Function Scan on user_event_version_set uevv (cost=0.25..10.25 rows=995 width=40) (actual time=4.089..4.121 rows=161 loops=1)

  • Filter: (id IS NOT NULL)
Planning time : 1.946 ms
Execution time : 1,248.703 ms