explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jVcy

Settings
# exclusive inclusive rows x rows loops node
1. 0.452 2,289.225 ↑ 61.7 301 1

Sort (cost=51,126.86..51,173.27 rows=18,565 width=225) (actual time=2,289.208..2,289.225 rows=301 loops=1)

  • Sort Key: pn.last_name, pn.first_name, pn.middle_name
  • Sort Method: quicksort Memory: 99kB
2. 2.002 2,288.773 ↑ 61.7 301 1

GroupAggregate (cost=46,616.76..47,777.07 rows=18,565 width=225) (actual time=2,286.658..2,288.773 rows=301 loops=1)

  • Group Key: (btrim(((((((((COALESCE(pn.prefix, ''::text) || ' '::text) || COALESCE(pn.first_name, ''::text)) || ' '::text) || COALESCE(pn.middle_name, ''::text)) || ' '::text) || COALESCE(pn.last_name, ''::text)) || ' '::text) || COALESCE(pn.suffix, ''::text)))), pn.id, full_data.person_version, full_data.author_id, full_data.comment, full_data.sex, full_data.person_thumbnail, full_data.person_user_id, full_data.user_id, full_data.newer_version
3. 4.265 2,286.771 ↑ 8.9 2,079 1

Sort (cost=46,616.76..46,663.17 rows=18,565 width=225) (actual time=2,286.635..2,286.771 rows=2,079 loops=1)

  • Sort Key: (btrim(((((((((COALESCE(pn.prefix, ''::text) || ' '::text) || COALESCE(pn.first_name, ''::text)) || ' '::text) || COALESCE(pn.middle_name, ''::text)) || ' '::text) || COALESCE(pn.last_name, ''::text)) || ' '::text) || COALESCE(pn.suffix, ''::text)))), pn.id, full_data.person_version, full_data.author_id, full_data.comment, full_data.sex, full_data.person_thumbnail, full_data.person_user_id, full_data.user_id, full_data.newer_version
  • Sort Method: quicksort Memory: 643kB
4. 2.625 2,282.506 ↑ 8.9 2,079 1

Hash Left Join (cost=17,674.58..43,266.97 rows=18,565 width=225) (actual time=2,109.384..2,282.506 rows=2,079 loops=1)

  • Hash Cond: (full_data.person_version = pn.person_version)
5. 1.142 2,279.390 ↑ 8.8 1,893 1

Hash Left Join (cost=17,639.63..42,421.40 rows=16,581 width=144) (actual time=2,108.871..2,279.390 rows=1,893 loops=1)

  • Hash Cond: (person_face.face_id = ifv.face_id)
  • Filter: ((ifv.image_id IS NULL) OR (uivv.id IS NOT NULL))
  • Rows Removed by Filter: 46
6. 137.357 184.126 ↑ 8.6 1,896 1

Nested Loop Left Join (cost=0.25..24,432.56 rows=16,250 width=144) (actual time=14.734..184.126 rows=1,896 loops=1)

  • Join Filter: (person_face.version_history && full_data.version_history)
  • Rows Removed by Join Filter: 547506
7. 14.735 14.735 ↑ 3.6 281 1

Function Scan on user_person_version_set full_data (cost=0.25..10.25 rows=1,000 width=168) (actual time=14.692..14.735 rows=281 loops=1)

8. 31.688 32.034 ↓ 1.2 1,955 281

Materialize (cost=0.00..51.38 rows=1,625 width=57) (actual time=0.000..0.114 rows=1,955 loops=281)

9. 0.346 0.346 ↓ 1.2 1,955 1

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

10. 0.418 2,094.122 ↑ 1.4 2,060 1

Hash (cost=17,602.10..17,602.10 rows=2,983 width=24) (actual time=2,094.122..2,094.122 rows=2,060 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
11. 0.671 2,093.704 ↑ 1.4 2,060 1

Hash Left Join (cost=17,514.03..17,602.10 rows=2,983 width=24) (actual time=2,092.792..2,093.704 rows=2,060 loops=1)

  • Hash Cond: (ifv.image_id = uivv.id)
12. 0.251 0.251 ↑ 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.005..0.251 rows=2,060 loops=1)

13. 0.154 2,092.782 ↓ 450.0 900 1

Hash (cost=17,514.01..17,514.01 rows=2 width=8) (actual time=2,092.782..2,092.782 rows=900 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
14. 0.146 2,092.628 ↓ 450.0 900 1

Subquery Scan on uivv (cost=17,513.87..17,514.01 rows=2 width=8) (actual time=2,091.919..2,092.628 rows=900 loops=1)

15. 0.496 2,092.482 ↓ 450.0 900 1

Unique (cost=17,513.87..17,513.99 rows=2 width=437) (actual time=2,091.917..2,092.482 rows=900 loops=1)

16.          

CTE data1

17. 0.622 1.887 ↓ 1.2 936 1

Hash Join (cost=114.57..139.33 rows=772 width=493) (actual time=1.174..1.887 rows=936 loops=1)

  • Hash Cond: (uiv.image_id = image.id)
18. 0.109 0.109 ↓ 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.109 rows=936 loops=1)

19. 0.711 1.156 ↑ 1.6 953 1

Hash (cost=95.92..95.92 rows=1,492 width=477) (actual time=1.156..1.156 rows=953 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 402kB
20. 0.445 0.445 ↑ 1.6 953 1

Seq Scan on image (cost=0.00..95.92 rows=1,492 width=477) (actual time=0.004..0.445 rows=953 loops=1)

21.          

CTE data2

22. 840.612 2,081.175 ↓ 2,802.0 2,802 1

Nested Loop Anti Join (cost=1.32..17,357.12 rows=1 width=485) (actual time=0.846..2,081.175 rows=2,802 loops=1)

  • Join Filter: ((image_1.version_history && data1_1.version_history) AND (data1_1.author_id = family_friend.family_member_id))
  • Rows Removed by Join Filter: 2623373
23. 3.685 929.319 ↓ 2,804.0 2,804 1

Nested Loop (cost=1.32..17,330.10 rows=1 width=485) (actual time=0.447..929.319 rows=2,804 loops=1)

24. 1.761 2.186 ↓ 16.4 2,824 1

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

  • Hash Cond: (uiv_1.user_id = family_friend.user_id)
25. 0.416 0.416 ↓ 1.2 936 1

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

  • Filter: (deleted IS NULL)
26. 0.005 0.009 ↓ 6.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.004 0.004 ↓ 6.0 12 1

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

28. 14.120 923.448 ↑ 1.0 1 2,824

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

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

SubPlan (for Index Scan)

30. 2.824 909.328 ↑ 1.0 1 2,824

Aggregate (cost=99.69..99.70 rows=1 width=8) (actual time=0.322..0.322 rows=1 loops=2,824)

31. 906.504 906.504 ↑ 15.0 1 2,824

Seq Scan on image iimage (cost=0.00..99.65 rows=15 width=8) (actual time=0.161..0.321 rows=1 loops=2,824)

  • Filter: (version_history && image_1.version_history)
  • Rows Removed by Filter: 952
32. 311.244 311.244 ↓ 1.2 936 2,804

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

33. 3.433 2,091.986 ↓ 450.0 900 1

Sort (cost=17.42..17.43 rows=2 width=437) (actual time=2,091.917..2,091.986 rows=900 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
  • Sort Method: quicksort Memory: 489kB
34. 0.134 2,088.553 ↓ 450.0 900 1

Append (cost=0.00..17.41 rows=2 width=437) (actual time=1.184..2,088.553 rows=900 loops=1)

35. 3.048 3.048 ↓ 771.0 771 1

CTE Scan on data1 (cost=0.00..17.37 rows=1 width=437) (actual time=1.184..3.048 rows=771 loops=1)

  • Filter: ((deleted IS NULL) AND (user_id = 4))
  • Rows Removed by Filter: 165
36. 2,085.371 2,085.371 ↓ 129.0 129 1

CTE Scan on data2 (cost=0.00..0.02 rows=1 width=437) (actual time=2.578..2,085.371 rows=129 loops=1)

  • Filter: (user_id = 4)
  • Rows Removed by Filter: 2673
37. 0.268 0.491 ↑ 1.0 998 1

Hash (cost=22.20..22.20 rows=1,020 width=57) (actual time=0.491..0.491 rows=998 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 92kB
38. 0.223 0.223 ↑ 1.0 998 1

Seq Scan on person_name pn (cost=0.00..22.20 rows=1,020 width=57) (actual time=0.008..0.223 rows=998 loops=1)

Planning time : 1.148 ms
Execution time : 2,290.097 ms