explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TIzS

Settings
# exclusive inclusive rows x rows loops node
1. 0.402 1,704.373 ↓ 1.5 158 1

Sort (cost=15,357.98..15,358.24 rows=104 width=525) (actual time=1,704.349..1,704.373 rows=158 loops=1)

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

CTE query_part

3. 0.005 1,418.401 ↓ 25.0 50 1

Limit (cost=14,914.68..14,914.76 rows=2 width=308) (actual time=1,418.365..1,418.401 rows=50 loops=1)

4. 0.030 1,418.396 ↓ 25.0 50 1

Unique (cost=14,914.68..14,914.76 rows=2 width=308) (actual time=1,418.364..1,418.396 rows=50 loops=1)

5. 0.727 1,418.366 ↓ 25.0 50 1

Sort (cost=14,914.68..14,914.69 rows=2 width=308) (actual time=1,418.363..1,418.366 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: 603kB
6. 0.237 1,417.639 ↓ 561.5 1,123 1

Subquery Scan on image_2 (cost=14,914.53..14,914.67 rows=2 width=308) (actual time=1,416.942..1,417.639 rows=1,123 loops=1)

7. 0.395 1,417.402 ↓ 561.5 1,123 1

Unique (cost=14,914.53..14,914.65 rows=2 width=437) (actual time=1,416.941..1,417.402 rows=1,123 loops=1)

8.          

CTE data1

9. 0.440 1.656 ↓ 1.1 1,183 1

Hash Join (cost=106.06..146.70 rows=1,114 width=489) (actual time=1.123..1.656 rows=1,183 loops=1)

  • Hash Cond: (uiv.image_id = image.id)
10. 0.104 0.104 ↑ 1.2 1,190 1

Seq Scan on user_image_version uiv (cost=0.00..36.75 rows=1,475 width=24) (actual time=0.004..0.104 rows=1,190 loops=1)

11. 0.665 1.112 ↓ 1.1 1,207 1

Hash (cost=92.14..92.14 rows=1,114 width=473) (actual time=1.112..1.112 rows=1,207 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 516kB
12. 0.447 0.447 ↓ 1.1 1,207 1

Seq Scan on image (cost=0.00..92.14 rows=1,114 width=473) (actual time=0.002..0.447 rows=1,207 loops=1)

13.          

CTE data2

14. 1,016.193 1,406.370 ↓ 3,489.0 3,489 1

Nested Loop Anti Join (cost=1.32..14,739.93 rows=1 width=481) (actual time=0.482..1,406.370 rows=3,489 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: 4,130,769
15. 4.047 33.789 ↓ 3,494.0 3,494 1

Nested Loop (cost=1.32..14,700.94 rows=1 width=481) (actual time=0.053..33.789 rows=3,494 loops=1)

16. 0.839 1.222 ↓ 10.9 3,565 1

Hash Join (cost=1.04..46.61 rows=328 width=16) (actual time=0.017..1.222 rows=3,565 loops=1)

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
17. 0.377 0.377 ↑ 1.2 1,190 1

Seq Scan on user_image_version uiv_1 (cost=0.00..36.75 rows=1,475 width=16) (actual time=0.004..0.377 rows=1,190 loops=1)

  • Filter: (deleted IS NULL)
18. 0.003 0.006 ↓ 6.0 12 1

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

  • Buckets: 1,024 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. 7.256 28.520 ↑ 1.0 1 3,565

Index Scan using image_pk on image image_1 (cost=0.28..44.64 rows=1 width=473) (actual time=0.008..0.008 rows=1 loops=3,565)

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

SubPlan (for Index Scan)

22. 3.544 21.264 ↑ 1.0 1 3,544

Aggregate (cost=44.09..44.10 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,544)

23. 3.544 17.720 ↑ 11.0 1 3,544

Bitmap Heap Scan on image iimage (cost=12.09..44.06 rows=11 width=8) (actual time=0.005..0.005 rows=1 loops=3,544)

  • Recheck Cond: (version_history && image_1.version_history)
  • Heap Blocks: exact=3,776
24. 14.176 14.176 ↑ 11.0 1 3,544

Bitmap Index Scan on idx_image_version_history (cost=0.00..12.08 rows=11 width=0) (actual time=0.004..0.004 rows=1 loops=3,544)

  • Index Cond: (version_history && image_1.version_history)
25. 356.388 356.388 ↓ 1.1 1,182 3,494

CTE Scan on data1 (cost=0.00..22.28 rows=1,114 width=40) (actual time=0.000..0.102 rows=1,182 loops=3,494)

26. 3.726 1,417.007 ↓ 563.0 1,126 1

Sort (cost=27.91..27.91 rows=2 width=437) (actual time=1,416.940..1,417.007 rows=1,126 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: 632kB
27. 0.132 1,413.281 ↓ 563.0 1,126 1

Append (cost=0.00..27.90 rows=2 width=437) (actual time=1.128..1,413.281 rows=1,126 loops=1)

28. 2.882 2.882 ↓ 127.0 127 1

CTE Scan on data1 data1_1 (cost=0.00..27.85 rows=1 width=437) (actual time=1.128..2.882 rows=127 loops=1)

  • Filter: ((deleted IS NULL) AND (image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 1,056
29. 1,410.267 1,410.267 ↓ 999.0 999 1

CTE Scan on data2 (cost=0.00..0.03 rows=1 width=437) (actual time=4.607..1,410.267 rows=999 loops=1)

  • Filter: ((image_type = ANY ('{photo,video}'::text[])) AND (user_id = 1))
  • Rows Removed by Filter: 2,490
30. 0.590 1,703.971 ↓ 1.5 158 1

Hash Left Join (cost=91.60..439.73 rows=104 width=525) (actual time=1,451.010..1,703.971 rows=158 loops=1)

  • Hash Cond: (query_part.id = image_event.image_id)
31. 0.207 1,688.470 ↓ 7.9 158 1

Hash Left Join (cost=20.75..365.46 rows=20 width=501) (actual time=1,436.080..1,688.470 rows=158 loops=1)

  • Hash Cond: (query_part.date_id = dates.id)
32. 0.250 1,688.057 ↓ 7.9 158 1

Nested Loop Left Join (cost=6.05..350.49 rows=20 width=490) (actual time=1,435.865..1,688.057 rows=158 loops=1)

  • Filter: (upvf.deleted IS NULL)
33. 1.707 1,429.939 ↓ 39.0 156 1

Nested Loop (cost=5.80..270.24 rows=4 width=534) (actual time=1,428.841..1,429.939 rows=156 loops=1)

  • Join Filter: (query_part.uploaded_by = users.user_id)
  • Rows Removed by Join Filter: 6,240
34. 0.038 0.038 ↓ 1.1 41 1

Seq Scan on users (cost=0.00..6.39 rows=39 width=26) (actual time=0.005..0.038 rows=41 loops=1)

35. 0.611 1,428.194 ↓ 39.0 156 41

Materialize (cost=5.80..261.52 rows=4 width=516) (actual time=34.609..34.834 rows=156 loops=41)

36. 0.175 1,427.583 ↓ 39.0 156 1

Nested Loop Left Join (cost=5.80..261.50 rows=4 width=516) (actual time=1,418.969..1,427.583 rows=156 loops=1)

37. 0.049 1,419.352 ↓ 38.0 152 1

Nested Loop Left Join (cost=5.52..103.05 rows=4 width=464) (actual time=1,418.905..1,419.352 rows=152 loops=1)

38. 0.258 1,418.999 ↓ 38.0 152 1

Hash Right Join (cost=5.24..100.88 rows=4 width=404) (actual time=1,418.890..1,418.999 rows=152 loops=1)

  • Hash Cond: (ifv.image_id = query_part.id)
39. 0.219 0.219 ↑ 1.1 2,543 1

Seq Scan on image_face_version ifv (cost=0.00..85.35 rows=2,735 width=16) (actual time=0.005..0.219 rows=2,543 loops=1)

40. 0.030 1,418.522 ↓ 25.0 50 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
41. 0.034 1,418.492 ↓ 25.0 50 1

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

  • Hash Cond: (location.id = query_part.location_id)
42. 0.017 0.017 ↓ 1.5 125 1

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

43. 0.018 1,418.441 ↓ 25.0 50 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
44. 1,418.423 1,418.423 ↓ 25.0 50 1

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

45. 0.304 0.304 ↑ 1.0 1 152

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

  • Index Cond: (id = ifv.face_id)
46. 8.056 8.056 ↑ 1.0 1 152

Index Scan using person_face_pkey on person_face (cost=0.28..39.60 rows=1 width=60) (actual time=0.032..0.053 rows=1 loops=152)

  • Index Cond: (f.id = face_id)
47. 257.868 257.868 ↑ 1,000.0 1 156

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

48. 0.110 0.206 ↓ 1.0 450 1

Hash (cost=9.31..9.31 rows=431 width=19) (actual time=0.206..0.206 rows=450 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
49. 0.096 0.096 ↓ 1.0 450 1

Seq Scan on dates (cost=0.00..9.31 rows=431 width=19) (actual time=0.007..0.096 rows=450 loops=1)

50. 0.050 14.911 ↑ 4.2 199 1

Hash (cost=60.33..60.33 rows=841 width=48) (actual time=14.911..14.911 rows=199 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
51. 0.068 14.861 ↑ 4.2 199 1

Hash Join (cost=22.69..60.33 rows=841 width=48) (actual time=14.757..14.861 rows=199 loops=1)

  • Hash Cond: (image_event.event_id = uevv.id)
52. 0.049 0.049 ↓ 1.2 199 1

Seq Scan on image_event (cost=0.00..7.69 rows=169 width=16) (actual time=0.006..0.049 rows=199 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 4
53. 0.061 14.744 ↑ 4.9 203 1

Hash (cost=10.25..10.25 rows=995 width=40) (actual time=14.744..14.744 rows=203 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
54. 14.683 14.683 ↑ 4.9 203 1

Function Scan on user_event_version_set uevv (cost=0.25..10.25 rows=995 width=40) (actual time=14.636..14.683 rows=203 loops=1)

  • Filter: (id IS NOT NULL)
Planning time : 1.835 ms
Execution time : 1,705.638 ms