explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q6kA

Settings
# exclusive inclusive rows x rows loops node
1. 1.547 29.559 ↓ 200.6 7,020 1

Hash Right Join (cost=918.72..945.86 rows=35 width=198) (actual time=27.474..29.559 rows=7,020 loops=1)

  • Hash Cond: ((likes_count.user_id)::text = (users.id)::text)
2.          

CTE ideas_count

3. 0.126 0.316 ↑ 1.3 233 1

HashAggregate (cost=88.62..91.61 rows=299 width=33) (actual time=0.282..0.316 rows=233 loops=1)

  • Group Key: ideas.author_id
4. 0.190 0.190 ↓ 1.1 355 1

Index Scan using idx_ideas_ideabox_id on ideas (cost=0.28..87.02 rows=321 width=50) (actual time=0.027..0.190 rows=355 loops=1)

  • Index Cond: ((ideabox_id)::text = '5c5ad8d2311b915403970f03'::text)
  • Filter: (state = 'publish'::idea_state)
  • Rows Removed by Filter: 563
5.          

CTE comments_count

6. 0.268 2.783 ↑ 1.1 246 1

HashAggregate (cost=182.03..184.66 rows=263 width=33) (actual time=2.751..2.783 rows=246 loops=1)

  • Group Key: comments.author_id
7. 0.523 2.515 ↓ 5.0 1,320 1

Hash Join (cost=91.03..180.72 rows=263 width=50) (actual time=2.059..2.515 rows=1,320 loops=1)

  • Hash Cond: ((comments.idea_id)::text = (ideas_1.id)::text)
8. 1.740 1.740 ↑ 1.0 3,301 1

Seq Scan on comments (cost=0.00..81.01 rows=3,301 width=75) (actual time=0.012..1.740 rows=3,301 loops=1)

9. 0.055 0.252 ↓ 1.1 355 1

Hash (cost=87.02..87.02 rows=321 width=25) (actual time=0.251..0.252 rows=355 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
10. 0.197 0.197 ↓ 1.1 355 1

Index Scan using idx_ideas_ideabox_id on ideas ideas_1 (cost=0.28..87.02 rows=321 width=25) (actual time=0.028..0.197 rows=355 loops=1)

  • Index Cond: ((ideabox_id)::text = '5c5ad8d2311b915403970f03'::text)
  • Filter: (state = 'publish'::idea_state)
  • Rows Removed by Filter: 563
11.          

CTE likes_count

12. 1.250 5.097 ↓ 1.3 1,534 1

HashAggregate (cost=482.37..493.78 rows=1,141 width=33) (actual time=4.903..5.097 rows=1,534 loops=1)

  • Group Key: idea_likes.user_id
13. 2.359 3.847 ↓ 4.7 5,414 1

Hash Join (cost=91.03..476.66 rows=1,141 width=50) (actual time=2.016..3.847 rows=5,414 loops=1)

  • Hash Cond: ((idea_likes.idea_id)::text = (ideas_2.id)::text)
14. 1.202 1.488 ↑ 1.0 14,304 1

Seq Scan on idea_likes (cost=0.00..348.04 rows=14,304 width=75) (actual time=0.012..1.488 rows=14,304 loops=1)

15. 0.070 0.286 ↓ 1.1 355 1

Hash (cost=87.02..87.02 rows=321 width=25) (actual time=0.286..0.286 rows=355 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
16. 0.216 0.216 ↓ 1.1 355 1

Index Scan using idx_ideas_ideabox_id on ideas ideas_2 (cost=0.28..87.02 rows=321 width=25) (actual time=0.030..0.216 rows=355 loops=1)

  • Index Cond: ((ideabox_id)::text = '5c5ad8d2311b915403970f03'::text)
  • Filter: (state = 'publish'::idea_state)
  • Rows Removed by Filter: 563
17. 5.458 5.458 ↓ 1.3 1,534 1

CTE Scan on likes_count (cost=0.00..22.82 rows=1,141 width=40) (actual time=4.905..5.458 rows=1,534 loops=1)

18. 3.041 22.554 ↓ 200.6 7,020 1

Hash (cost=148.22..148.22 rows=35 width=190) (actual time=22.554..22.554 rows=7,020 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1532kB
19. 1.374 19.513 ↓ 200.6 7,020 1

Hash Right Join (cost=141.97..148.22 rows=35 width=190) (actual time=18.061..19.513 rows=7,020 loops=1)

  • Hash Cond: ((comments_count.author_id)::text = (users.id)::text)
20. 2.849 2.849 ↑ 1.1 246 1

CTE Scan on comments_count (cost=0.00..5.26 rows=263 width=40) (actual time=2.759..2.849 rows=246 loops=1)

21. 3.902 15.290 ↓ 200.6 7,020 1

Hash (cost=141.53..141.53 rows=35 width=182) (actual time=15.290..15.290 rows=7,020 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1530kB
22. 2.149 11.388 ↓ 200.6 7,020 1

Hash Right Join (cost=134.76..141.53 rows=35 width=182) (actual time=9.166..11.388 rows=7,020 loops=1)

  • Hash Cond: ((ideas_count.author_id)::text = (users.id)::text)
23. 0.373 0.373 ↑ 1.3 233 1

CTE Scan on ideas_count (cost=0.00..5.98 rows=299 width=40) (actual time=0.284..0.373 rows=233 loops=1)

24. 4.534 8.866 ↓ 200.6 7,020 1

Hash (cost=134.33..134.33 rows=35 width=174) (actual time=8.866..8.866 rows=7,020 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1527kB
25. 3.769 4.332 ↓ 200.6 7,020 1

Bitmap Heap Scan on users (cost=8.30..134.33 rows=35 width=174) (actual time=0.627..4.332 rows=7,020 loops=1)

  • Recheck Cond: (ideabox_ids @> ARRAY[('5c5ad8d2311b915403970f03'::character varying)::mongo_id])
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=690
26. 0.563 0.563 ↓ 180.0 7,021 1

Bitmap Index Scan on idx_users_ideabox_ids (cost=0.00..8.29 rows=39 width=0) (actual time=0.563..0.563 rows=7,021 loops=1)

  • Index Cond: (ideabox_ids @> ARRAY[('5c5ad8d2311b915403970f03'::character varying)::mongo_id])