explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uLGX

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 34,243.779 ↑ 1.0 20 1

Limit (cost=723,872.82..723,872.87 rows=20 width=5,471) (actual time=34,243.764..34,243.779 rows=20 loops=1)

2. 347.091 34,243.768 ↑ 48.8 20 1

Sort (cost=723,872.82..723,875.26 rows=975 width=5,471) (actual time=34,243.764..34,243.768 rows=20 loops=1)

  • Sort Key: files.id
  • Sort Method: top-N heapsort Memory: 63kB
3. 155.006 33,896.677 ↓ 78.7 76,778 1

Nested Loop Left Join (cost=30,400.97..723,846.87 rows=975 width=5,471) (actual time=8,684.751..33,896.677 rows=76,778 loops=1)

  • Join Filter: (f.public_id = files.public_id)
4. 730.191 33,741.671 ↓ 78.7 76,778 1

Hash Join (cost=30,384.80..723,816.06 rows=975 width=3,153) (actual time=8,684.725..33,741.671 rows=76,778 loops=1)

  • Hash Cond: (files.public_id = function_test_one.asd_file_public_id)
5. 2,353.510 24,387.940 ↑ 1.0 1,668,181 1

Hash Left Join (cost=30,362.05..510,844.91 rows=1,670,107 width=3,149) (actual time=60.049..24,387.940 rows=1,668,181 loops=1)

  • Hash Cond: (files.deleted_by = users_deleted_by.id)
6. 2,873.963 22,034.038 ↑ 1.0 1,668,181 1

Hash Join (cost=30,325.13..506,423.40 rows=1,670,107 width=2,807) (actual time=59.636..22,034.038 rows=1,668,181 loops=1)

  • Hash Cond: (files.updated_by = users_updated_by.id)
7. 2,674.886 19,159.623 ↑ 1.0 1,668,181 1

Hash Join (cost=30,288.21..501,977.74 rows=1,670,107 width=2,465) (actual time=59.162..19,159.623 rows=1,668,181 loops=1)

  • Hash Cond: (files.created_by = users_created_by.id)
8. 2,737.125 16,484.393 ↑ 1.0 1,668,181 1

Merge Join (cost=30,251.29..497,532.08 rows=1,670,107 width=2,123) (actual time=58.804..16,484.393 rows=1,668,181 loops=1)

  • Merge Cond: (files.public_id = file_public_attributes.file_public_id)
9. 1,190.816 11,430.621 ↑ 1.0 1,668,181 1

Merge Left Join (cost=30,244.30..348,201.15 rows=1,670,107 width=1,113) (actual time=58.767..11,430.621 rows=1,668,181 loops=1)

  • Merge Cond: (files.public_id = file_default_watermarks.file_public_id)
10. 1,868.124 10,155.758 ↑ 1.0 1,667,562 1

Merge Left Join (cost=5.88..312,992.19 rows=1,670,107 width=1,048) (actual time=0.064..10,155.758 rows=1,667,562 loops=1)

  • Merge Cond: (files.public_id = files_external_data.file_public_id)
11. 2,172.148 7,188.309 ↑ 1.0 1,667,562 1

Merge Join (cost=5.46..225,361.94 rows=1,670,107 width=968) (actual time=0.045..7,188.309 rows=1,667,562 loops=1)

  • Merge Cond: (files.public_id = file_curations.file_public_id)
12. 3,563.544 3,563.544 ↑ 1.0 1,667,596 1

Index Scan using files_public_id_index on files (cost=0.43..124,242.87 rows=1,672,780 width=220) (actual time=0.023..3,563.544 rows=1,667,596 loops=1)

  • Filter: ((version_id IS NULL) AND (version_id IS NULL) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 41795
13. 1,452.617 1,452.617 ↓ 1.0 1,709,595 1

Index Scan using file_curations_file_public_id_index on file_curations (cost=0.43..75,974.73 rows=1,709,593 width=748) (actual time=0.014..1,452.617 rows=1,709,595 loops=1)

14. 1,099.325 1,099.325 ↓ 1.0 1,697,964 1

Index Scan using files_external_data_file_public_id_unique on files_external_data (cost=0.43..62,649.14 rows=1,697,962 width=80) (actual time=0.014..1,099.325 rows=1,697,964 loops=1)

15. 10.233 84.047 ↑ 1.0 45,131 1

Materialize (cost=30,238.42..30,468.90 rows=46,096 width=65) (actual time=58.694..84.047 rows=45,131 loops=1)

16. 58.854 73.814 ↑ 1.0 45,130 1

Sort (cost=30,238.42..30,353.66 rows=46,096 width=65) (actual time=58.688..73.814 rows=45,130 loops=1)

  • Sort Key: file_default_watermarks.file_public_id
  • Sort Method: external merge Disk: 3632kB
17. 9.125 14.960 ↑ 1.0 45,130 1

Bitmap Heap Scan on file_default_watermarks (cost=865.67..24,774.24 rows=46,096 width=65) (actual time=6.050..14.960 rows=45,130 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1277
18. 5.835 5.835 ↑ 1.0 45,130 1

Bitmap Index Scan on file_default_watermarks_user_id_index (cost=0.00..854.15 rows=46,096 width=0) (actual time=5.835..5.835 rows=45,130 loops=1)

  • Index Cond: (user_id = 1)
19. 2,316.647 2,316.647 ↓ 1.0 1,723,379 1

Index Scan using file_public_attributes_file_public_id_index on file_public_attributes (cost=0.43..124,149.45 rows=1,723,189 width=1,010) (actual time=0.030..2,316.647 rows=1,723,379 loops=1)

20. 0.237 0.344 ↑ 1.0 752 1

Hash (cost=27.52..27.52 rows=752 width=342) (actual time=0.344..0.344 rows=752 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 159kB
21. 0.107 0.107 ↑ 1.0 752 1

Seq Scan on users users_created_by (cost=0.00..27.52 rows=752 width=342) (actual time=0.004..0.107 rows=752 loops=1)

22. 0.241 0.452 ↑ 1.0 752 1

Hash (cost=27.52..27.52 rows=752 width=342) (actual time=0.452..0.452 rows=752 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 159kB
23. 0.211 0.211 ↑ 1.0 752 1

Seq Scan on users users_updated_by (cost=0.00..27.52 rows=752 width=342) (actual time=0.036..0.211 rows=752 loops=1)

24. 0.232 0.392 ↑ 1.0 752 1

Hash (cost=27.52..27.52 rows=752 width=342) (actual time=0.392..0.392 rows=752 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 159kB
25. 0.160 0.160 ↑ 1.0 752 1

Seq Scan on users users_deleted_by (cost=0.00..27.52 rows=752 width=342) (actual time=0.013..0.160 rows=752 loops=1)

26. 16.129 8,623.540 ↓ 79.5 79,471 1

Hash (cost=10.25..10.25 rows=1,000 width=4) (actual time=8,623.540..8,623.540 rows=79,471 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3818kB
27. 8,607.411 8,607.411 ↓ 79.5 79,471 1

Function Scan on function_test_one (cost=0.25..10.25 rows=1,000 width=4) (actual time=8,594.731..8,607.411 rows=79,471 loops=1)

28. 0.000 0.000 ↓ 0.0 0 76,778

Materialize (cost=16.17..16.20 rows=1 width=2,314) (actual time=0.000..0.000 rows=0 loops=76,778)

29. 0.000 0.020 ↓ 0.0 0 1

Unique (cost=16.17..16.18 rows=1 width=2,314) (actual time=0.020..0.020 rows=0 loops=1)

30. 0.007 0.020 ↓ 0.0 0 1

Sort (cost=16.17..16.18 rows=1 width=2,314) (actual time=0.020..0.020 rows=0 loops=1)

  • Sort Key: f.public_id
  • Sort Method: quicksort Memory: 25kB
31. 0.001 0.013 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..16.16 rows=1 width=2,314) (actual time=0.012..0.013 rows=0 loops=1)

  • Join Filter: (fv_title.file_version_id = fv.id)
32. 0.000 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..14.85 rows=1 width=1,802) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: (fv_subtext.file_version_id = fv.id)
33. 0.000 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..13.54 rows=1 width=1,286) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: (fv_alternative_text.file_version_id = fv.id)
34. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=1.40..12.23 rows=1 width=770) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: (fv.id = fv_caption.file_version_id)
35. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.40..10.91 rows=1 width=254) (actual time=0.011..0.011 rows=0 loops=1)

36. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.13..10.62 rows=1 width=239) (actual time=0.011..0.011 rows=0 loops=1)

37. 0.000 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.85..10.33 rows=1 width=224) (actual time=0.010..0.010 rows=0 loops=1)

38. 0.001 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.43..9.70 rows=1 width=224) (actual time=0.010..0.010 rows=0 loops=1)

39. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on file_versions fv (cost=0.00..1.25 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: ((version_id IS NULL) AND (entity_type = '-1'::integer) AND (entity_id = '-1'::integer))
  • Rows Removed by Filter: 17
40. 0.000 0.000 ↓ 0.0 0

Index Scan using files_pkey on files f (cost=0.43..8.45 rows=1 width=220) (never executed)

  • Index Cond: (id = fv.file_id)
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using file_public_attributes_file_public_id_index on file_public_attributes fpa (cost=0.43..0.62 rows=1 width=4) (never executed)

  • Index Cond: (file_public_id = f.public_id)
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_created_by_1 (cost=0.28..0.29 rows=1 width=19) (never executed)

  • Index Cond: (id = f.created_by)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users users_updated_by_1 (cost=0.28..0.29 rows=1 width=19) (never executed)

  • Index Cond: (id = f.updated_by)
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_caption (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 200)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_alternative_text (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 300)
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_subtext (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 400)
47. 0.000 0.000 ↓ 0.0 0

Seq Scan on file_version_values fv_title (cost=0.00..1.30 rows=1 width=520) (never executed)

  • Filter: (type = 500)
Planning time : 33.532 ms