explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xp7H

Settings
# exclusive inclusive rows x rows loops node
1. 12.287 248,807.617 ↑ 1.0 295 1

Index Scan using library_short_name_key on library (cost=2,255.25..18,780,489.48 rows=295 width=118) (actual time=1,048.343..248,807.617 rows=295 loops=1)

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 42
2.          

CTE users-library

3. 0.391 0.543 ↓ 1.0 178 1

Hash Join (cost=8.98..22.04 rows=176 width=95) (actual time=0.150..0.543 rows=178 loops=1)

  • Hash Cond: (reports_userprofile.user_id = auth_user.id)
4. 0.041 0.041 ↓ 1.0 178 1

Seq Scan on reports_userprofile (cost=0.00..9.76 rows=176 width=4) (actual time=0.008..0.041 rows=178 loops=1)

5. 0.061 0.111 ↓ 1.0 178 1

Hash (cost=6.77..6.77 rows=177 width=49) (actual time=0.111..0.111 rows=178 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
6. 0.050 0.050 ↓ 1.0 178 1

Seq Scan on auth_user (cost=0.00..6.77 rows=177 width=49) (actual time=0.006..0.050 rows=178 loops=1)

7.          

CTE _comment_apilogs

8. 0.271 8.265 ↓ 21.5 322 1

Sort (cost=2,232.90..2,232.93 rows=15 width=174) (actual time=8.210..8.265 rows=322 loops=1)

  • Sort Key: reports_apilog.date_time DESC
  • Sort Method: quicksort Memory: 192kB
9. 0.119 7.994 ↓ 21.5 322 1

Hash Join (cost=6.57..2,232.60 rows=15 width=174) (actual time=0.920..7.994 rows=322 loops=1)

  • Hash Cond: ((reports_apilog.username)::text = ("users-library".username)::text)
10. 0.302 7.188 ↓ 22.8 342 1

Nested Loop Anti Join (cost=0.85..2,226.68 rows=15 width=142) (actual time=0.211..7.188 rows=342 loops=1)

11. 0.330 0.330 ↓ 5.5 596 1

Index Scan using reports_apilog_ref_resource_name_8d7aaa12 on reports_apilog (cost=0.42..1,621.91 rows=108 width=146) (actual time=0.028..0.330 rows=596 loops=1)

  • Index Cond: ((ref_resource_name)::text = 'library'::text)
  • Filter: (comment IS NOT NULL)
  • Rows Removed by Filter: 198
12. 6.556 6.556 ↓ 0.0 0 596

Index Only Scan using reports_logdiff_log_id_4218eabe on reports_logdiff (cost=0.42..8.42 rows=2 width=4) (actual time=0.011..0.011 rows=0 loops=596)

  • Index Cond: (log_id = reports_apilog.id)
  • Heap Fetches: 254
13. 0.052 0.687 ↓ 1.0 178 1

Hash (cost=3.52..3.52 rows=176 width=350) (actual time=0.687..0.687 rows=178 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
14. 0.635 0.635 ↓ 1.0 178 1

CTE Scan on "users-library" (cost=0.00..3.52 rows=176 width=350) (actual time=0.153..0.635 rows=178 loops=1)

15.          

SubPlan (forIndex Scan)

16. 836.620 248,636.620 ↑ 1.0 1 295

Aggregate (cost=63,654.14..63,654.15 rows=1 width=8) (actual time=842.836..842.836 rows=1 loops=295)

17. 247,800.000 247,800.000 ↓ 1.1 5,912 295

Seq Scan on well w (cost=0.00..63,640.07 rows=5,626 width=4) (actual time=111.219..840.000 rows=5,912 loops=295)

  • Filter: (library_id = library.library_id)
  • Rows Removed by Filter: 2087848
18. 31.565 158.710 ↑ 1.0 1 295

Aggregate (cost=0.35..0.36 rows=1 width=32) (actual time=0.538..0.538 rows=1 loops=295)

19. 127.145 127.145 ↑ 1.0 1 295

CTE Scan on _comment_apilogs (cost=0.00..0.34 rows=1 width=72) (actual time=0.187..0.431 rows=1 loops=295)

  • Filter: ((key)::text = library.short_name)
  • Rows Removed by Filter: 321