explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G2zJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5.863 ↑ 1.0 1 1

Result (cost=582.70..582.71 rows=1 width=32) (actual time=5.863..5.863 rows=1 loops=1)

2.          

CTE matches

3. 0.005 0.105 ↓ 3.4 24 1

Nested Loop (cost=6.12..17.80 rows=7 width=46) (actual time=0.070..0.105 rows=24 loops=1)

  • Join Filter: (media.id = media_1.id)
4. 0.011 0.076 ↓ 3.4 24 1

Hash Join (cost=5.97..14.52 rows=7 width=62) (actual time=0.066..0.076 rows=24 loops=1)

  • Hash Cond: (user_media_offline.media_id = media.id)
5. 0.014 0.014 ↑ 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.014 rows=27 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
7. 0.042 0.042 ↓ 2.0 46 1

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

  • Filter: ((state = 'ACTIVE'::text) AND (lifecycle_status = 'ACTIVE'::text))
  • Rows Removed by Filter: 48
8. 0.024 0.024 ↑ 1.0 1 24

Index Only Scan using media_pkey on media media_1 (cost=0.14..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=24)

  • Index Cond: (id = user_media_offline.media_id)
  • Heap Fetches: 0
9.          

CTE view

10. 0.029 0.143 ↓ 3.4 24 1

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

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

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

12.          

Initplan (forResult)

13. 0.649 5.857 ↑ 1.0 1 1

Aggregate (cost=564.63..564.64 rows=1 width=32) (actual time=5.857..5.857 rows=1 loops=1)

14. 0.016 5.208 ↓ 3.4 24 1

Nested Loop Left Join (cost=76.63..564.61 rows=7 width=80) (actual time=0.519..5.208 rows=24 loops=1)

15. 0.152 0.152 ↓ 3.4 24 1

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

16. 0.120 5.040 ↑ 1.0 1 24

Nested Loop Left Join (cost=76.63..80.63 rows=1 width=48) (actual time=0.209..0.210 rows=1 loops=24)

17. 0.024 0.456 ↑ 1.0 1 24

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

18. 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)
19. 0.024 0.384 ↑ 1.0 1 24

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

20. 0.072 0.360 ↑ 2.0 1 24

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

  • Sort Key: media_version.version DESC
  • Sort Method: quicksort Memory: 25kB
21. 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
22. 0.336 4.464 ↑ 1.0 1 24

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

23. 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)

24. 0.066 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)

25. 0.960 3.264 ↓ 1.2 5 24

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

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

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

  • Filter: (state <> 'DELETED'::text)
  • Rows Removed by Filter: 21
27. 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
28. 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
29. 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)
30. 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)