explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nb3s

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 11.173 ↑ 1.0 1 1

Result (cost=2,051.14..2,051.15 rows=1 width=32) (actual time=11.173..11.173 rows=1 loops=1)

2.          

CTE matches

3. 0.012 0.072 ↓ 3.4 24 1

Hash Join (cost=5.97..14.52 rows=7 width=46) (actual time=0.063..0.072 rows=24 loops=1)

  • Hash Cond: (user_media_offline.media_id = media.id)
4. 0.013 0.013 ↑ 1.0 27 1

Index Only Scan using user_media_offline_pkey on user_media_offline (cost=0.28..8.75 rows=27 width=16) (actual time=0.010..0.013 rows=27 loops=1)

  • Index Cond: (user_id = '3b634574-260d-4d74-880d-075c1be5a8cf'::uuid)
  • Heap Fetches: 0
5. 0.009 0.047 ↓ 2.0 46 1

Hash (cost=5.41..5.41 rows=23 width=46) (actual time=0.047..0.047 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
6. 0.038 0.038 ↓ 2.0 46 1

Seq Scan on media (cost=0.00..5.41 rows=23 width=46) (actual time=0.004..0.038 rows=46 loops=1)

  • Filter: ((state = 'ACTIVE'::text) AND (lifecycle_status = 'ACTIVE'::text))
  • Rows Removed by Filter: 48
7.          

CTE view

8. 0.030 0.109 ↓ 3.4 24 1

Sort (cost=0.24..0.26 rows=7 width=48) (actual time=0.106..0.109 rows=24 loops=1)

  • Sort Key: matches.name, matches.id
  • Sort Method: quicksort Memory: 27kB
9. 0.079 0.079 ↓ 3.4 24 1

CTE Scan on matches (cost=0.00..0.14 rows=7 width=48) (actual time=0.063..0.079 rows=24 loops=1)

10.          

Initplan (forResult)

11. 1.871 11.158 ↑ 1.0 1 1

Aggregate (cost=2,036.35..2,036.37 rows=1 width=32) (actual time=11.158..11.158 rows=1 loops=1)

12. 0.025 9.287 ↓ 3.4 24 1

Nested Loop Left Join (cost=286.84..2,036.33 rows=7 width=80) (actual time=0.649..9.287 rows=24 loops=1)

13. 0.118 0.118 ↓ 3.4 24 1

CTE Scan on view (cost=0.00..0.14 rows=7 width=48) (actual time=0.107..0.118 rows=24 loops=1)

14. 0.120 9.144 ↑ 1.0 1 24

Nested Loop Left Join (cost=286.84..290.87 rows=1 width=48) (actual time=0.380..0.381 rows=1 loops=24)

15. 0.024 4.536 ↑ 1.0 1 24

Nested Loop Left Join (cost=216.17..219.67 rows=1 width=226) (actual time=0.188..0.189 rows=1 loops=24)

16. 0.048 0.480 ↑ 1.0 1 24

Nested Loop Left Join (cost=5.95..9.42 rows=1 width=194) (actual time=0.019..0.020 rows=1 loops=24)

17. 0.048 0.048 ↑ 1.0 1 24

Index Scan using media_pkey on media vm (cost=0.14..3.59 rows=1 width=174) (actual time=0.002..0.002 rows=1 loops=24)

  • Index Cond: (id = view.id)
  • Filter: (state <> 'DELETED'::text)
18. 0.000 0.384 ↑ 1.0 1 24

Limit (cost=5.81..5.81 rows=1 width=52) (actual time=0.016..0.016 rows=1 loops=24)

19. 0.096 0.384 ↑ 2.0 1 24

Sort (cost=5.81..5.81 rows=2 width=52) (actual time=0.016..0.016 rows=1 loops=24)

  • Sort Key: media_version.version DESC
  • Sort Method: quicksort Memory: 25kB
20. 0.288 0.288 ↑ 1.0 2 24

Seq Scan on media_version (cost=0.00..5.80 rows=2 width=52) (actual time=0.004..0.012 rows=2 loops=24)

  • Filter: (media_id = vm.id)
  • Rows Removed by Filter: 142
21. 0.336 4.032 ↑ 1.0 1 24

Aggregate (cost=210.22..210.23 rows=1 width=32) (actual time=0.168..0.168 rows=1 loops=24)

22. 0.038 3.696 ↑ 1.7 6 24

Nested Loop Left Join (cost=37.01..210.19 rows=10 width=68) (actual time=0.026..0.154 rows=6 loops=24)

23. 0.960 2.664 ↑ 1.7 6 24

Hash Join (cost=20.60..42.58 rows=10 width=52) (actual time=0.017..0.111 rows=6 loops=24)

  • Hash Cond: (organizational_unit.id = media_assignment.organizational_unit_id)
24. 1.536 1.536 ↑ 1.0 460 24

Seq Scan on organizational_unit (cost=0.00..20.76 rows=460 width=29) (actual time=0.001..0.064 rows=460 loops=24)

  • Filter: (state <> 'DELETED'::text)
  • Rows Removed by Filter: 1
25. 0.048 0.168 ↑ 1.7 6 24

Hash (cost=20.48..20.48 rows=10 width=39) (actual time=0.007..0.007 rows=6 loops=24)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.072 0.120 ↑ 1.7 6 24

Bitmap Heap Scan on media_assignment (cost=4.35..20.48 rows=10 width=39) (actual time=0.004..0.005 rows=6 loops=24)

  • Recheck Cond: (media_id = vm.id)
  • Heap Blocks: exact=27
27. 0.048 0.048 ↑ 1.7 6 24

Bitmap Index Scan on media_assignment_pkey (cost=0.00..4.35 rows=10 width=0) (actual time=0.002..0.002 rows=6 loops=24)

  • Index Cond: (media_id = vm.id)
28. 0.142 0.994 ↑ 1.0 1 142

Nested Loop Left Join (cost=16.41..16.75 rows=1 width=48) (actual time=0.006..0.007 rows=1 loops=142)

29. 0.142 0.710 ↑ 1.0 1 142

Nested Loop (cost=0.53..0.84 rows=1 width=164) (actual time=0.005..0.005 rows=1 loops=142)

30. 0.000 0.426 ↑ 1.0 1 142

Nested Loop (cost=0.40..0.64 rows=1 width=145) (actual time=0.003..0.003 rows=1 loops=142)

31. 0.284 0.284 ↑ 1.0 1 142

Index Scan using pk_organizational_unit on organizational_unit ou (cost=0.27..0.46 rows=1 width=161) (actual time=0.002..0.002 rows=1 loops=142)

  • Index Cond: (organizational_unit.id = id)
  • Filter: (state <> 'DELETED'::text)
32. 0.142 0.142 ↑ 1.0 1 142

Index Scan using pk_organizational_hierarchy on organizational_hierarchy (cost=0.12..0.17 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=142)

  • Index Cond: (id = ou.organizational_hierarchy_id)
  • Filter: (state = 'ACTIVE'::text)
33. 0.142 0.142 ↑ 1.0 1 142

Index Scan using pk_organizational_unit_type on organizational_unit_type (cost=0.13..0.18 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=142)

  • Index Cond: (id = ou.organizational_unit_type_id)
  • Filter: (state = 'ACTIVE'::text)
34. 0.142 0.142 ↑ 1.0 1 142

Aggregate (cost=15.88..15.89 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=142)

35. 0.000 0.000 ↓ 0.0 0 142

Seq Scan on organizational_unit_tag (cost=0.00..15.88 rows=2 width=32) (actual time=0.000..0.000 rows=0 loops=142)

  • Filter: (organizational_unit_id = ou.id)
36. 0.360 4.488 ↑ 1.0 1 24

Aggregate (cost=70.67..70.68 rows=1 width=32) (actual time=0.187..0.187 rows=1 loops=24)

37. 0.021 4.128 ↓ 1.2 5 24

Nested Loop Left Join (cost=12.22..69.66 rows=4 width=64) (actual time=0.077..0.172 rows=5 loops=24)

38. 0.090 3.552 ↓ 1.2 5 24

Nested Loop Left Join (cost=11.80..66.47 rows=4 width=48) (actual time=0.068..0.148 rows=5 loops=24)

39. 0.960 3.240 ↓ 1.2 5 24

Hash Right Join (cost=11.38..35.36 rows=4 width=48) (actual time=0.064..0.135 rows=5 loops=24)

  • Hash Cond: (sec_user.id = media_change_reason.rec_editor)
40. 1.656 1.656 ↑ 1.0 434 24

Seq Scan on sec_user (cost=0.00..20.69 rows=434 width=32) (actual time=0.001..0.069 rows=434 loops=24)

  • Filter: (state <> 'DELETED'::text)
  • Rows Removed by Filter: 21
41. 0.048 0.624 ↓ 1.2 5 24

Hash (cost=11.32..11.32 rows=4 width=48) (actual time=0.026..0.026 rows=5 loops=24)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.576 0.576 ↓ 1.2 5 24

Seq Scan on media_change_reason (cost=0.00..11.32 rows=4 width=48) (actual time=0.005..0.024 rows=5 loops=24)

  • Filter: (media_id = vm.id)
  • Rows Removed by Filter: 341
43. 0.222 0.222 ↑ 1.0 1 111

Index Scan using pk_person on person (cost=0.42..7.78 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=111)

  • Index Cond: (id = sec_user.person_id)
  • Filter: (state <> 'DELETED'::text)
44. 0.555 0.555 ↑ 1.0 1 111

Index Scan using pk_person on person person_1 (cost=0.42..0.80 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=111)

  • Index Cond: (person.id = id)