explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qYIp

Settings
# exclusive inclusive rows x rows loops node
1. 292.900 3,116.269 ↓ 51.0 51 1

Limit (cost=1,832,803.03..1,832,803.05 rows=1 width=96) (actual time=3,113.836..3,116.269 rows=51 loops=1)

2. 3.812 2,823.369 ↓ 51.0 51 1

WindowAgg (cost=1,832,803.03..1,832,803.05 rows=1 width=96) (actual time=2,823.362..2,823.369 rows=51 loops=1)

3. 2.516 2,819.557 ↓ 24,993.0 24,993 1

Subquery Scan on n (cost=1,832,803.03..1,832,803.04 rows=1 width=88) (actual time=2,814.040..2,819.557 rows=24,993 loops=1)

4. 12.378 2,817.041 ↓ 24,993.0 24,993 1

Sort (cost=1,832,803.03..1,832,803.03 rows=1 width=96) (actual time=2,814.031..2,817.041 rows=24,993 loops=1)

  • Sort Key: outfit.outfit_post_time DESC
  • Sort Method: external merge Disk: 2600kB
5. 7.483 2,804.663 ↓ 24,993.0 24,993 1

Merge Join (cost=1,820,634.41..1,832,803.02 rows=1 width=96) (actual time=2,680.664..2,804.663 rows=24,993 loops=1)

  • Merge Cond: (outfit_comment.id_outfit = outfit.id_outfit)
6. 45.838 2,760.043 ↑ 24.3 24,993 1

Unique (cost=1,811,922.03..1,816,485.12 rows=608,412 width=16) (actual time=2,649.687..2,760.043 rows=24,993 loops=1)

7. 196.166 2,714.205 ↓ 1.0 611,744 1

Sort (cost=1,811,922.03..1,813,443.06 rows=608,412 width=16) (actual time=2,649.686..2,714.205 rows=611,744 loops=1)

  • Sort Key: outfit_comment.id_outfit, rel_outfit_comment_hashtag.id_hashtag
  • Sort Method: external merge Disk: 15608kB
8. 4.605 2,518.039 ↓ 1.0 611,744 1

Gather (cost=1,000.00..1,743,071.30 rows=608,412 width=16) (actual time=0.464..2,518.039 rows=611,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 16.694 2,513.434 ↑ 3.0 203,915 3 / 3

Parallel Append (cost=0.00..1,681,230.10 rows=608,412 width=16) (actual time=576.598..2,513.434 rows=203,915 loops=3)

10. 21.914 542.333 ↓ 2.4 113,273 1 / 3

Parallel Hash Join (cost=14,710.39..324,286.08 rows=46,962 width=16) (actual time=1,585.255..1,627.000 rows=113,273 loops=1)

  • Hash Cond: (rel_outfit_comment_hashtag.id_outfit_comment = outfit_comment.id_outfit_comment)
11. 442.374 442.374 ↓ 2.4 113,273 1 / 3

Parallel Seq Scan on rel_outfit_comment_hashtag (cost=0.00..308,400.42 rows=46,962 width=16) (actual time=0.043..1,327.121 rows=113,273 loops=1)

  • Filter: ((deleted_at IS NULL) AND (id_hashtag = 120))
  • Rows Removed by Filter: 22555671
12. 13.775 78.046 ↓ 2.4 290,815 1 / 3

Parallel Hash (cost=12,603.73..12,603.73 rows=121,173 width=16) (actual time=234.137..234.137 rows=290,815 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2752kB
13. 64.271 64.271 ↓ 2.4 290,815 1 / 3

Parallel Seq Scan on outfit_comment (cost=0.00..12,603.73 rows=121,173 width=16) (actual time=143.607..192.812 rows=290,815 loops=1)

  • Filter: (deleted_at IS NULL)
14. 1,954.407 1,954.407 ↑ 1.2 166,157 3 / 3

Parallel Seq Scan on rel_outfit_hashtag (cost=0.00..1,347,817.83 rows=206,543 width=16) (actual time=48.194..1,954.407 rows=166,157 loops=3)

  • Filter: ((deleted_at IS NULL) AND (id_hashtag = 120))
  • Rows Removed by Filter: 33003689
15. 3.416 37.137 ↓ 24,995.0 24,995 1

Materialize (cost=8,712.38..8,712.39 rows=1 width=96) (actual time=30.959..37.137 rows=24,995 loops=1)

16. 11.789 33.721 ↓ 24,995.0 24,995 1

Sort (cost=8,712.38..8,712.38 rows=1 width=96) (actual time=30.957..33.721 rows=24,995 loops=1)

  • Sort Key: outfit.id_outfit
  • Sort Method: external merge Disk: 2600kB
17. 18.254 21.932 ↓ 24,996.0 24,996 1

Nested Loop Left Join (cost=583.36..8,712.37 rows=1 width=96) (actual time=0.377..21.932 rows=24,996 loops=1)

  • Join Filter: (((outfit.id_user_app = user_block.id_block_from) AND (user_block.id_block_to = 1)) OR ((outfit.id_user_app = user_block.id_block_to) AND (user_block.id_block_from = 1)))
  • Rows Removed by Join Filter: 124980
  • Filter: (user_block.id_user_block IS NULL)
18. 3.678 3.678 ↑ 1.0 24,996 1

Seq Scan on outfit (cost=0.00..2,466.96 rows=24,996 width=96) (actual time=0.011..3.678 rows=24,996 loops=1)

  • Filter: ((deleted_at IS NULL) AND (NOT private_flag))
19. 0.000 0.000 ↑ 2.0 5 24,996

Materialize (cost=583.36..621.33 rows=10 width=24) (actual time=0.000..0.000 rows=5 loops=24,996)

20. 0.008 0.360 ↑ 2.0 5 1

Bitmap Heap Scan on user_block (cost=583.36..621.28 rows=10 width=24) (actual time=0.357..0.360 rows=5 loops=1)

  • Recheck Cond: ((id_block_to = 1) OR (id_block_from = 1))
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=5
21. 0.001 0.352 ↓ 0.0 0 1

BitmapOr (cost=583.36..583.36 rows=10 width=0) (actual time=0.352..0.352 rows=0 loops=1)

22. 0.346 0.346 ↓ 0.0 0 1

Bitmap Index Scan on user_block_id_block_from_idx (cost=0.00..579.04 rows=6 width=0) (actual time=0.346..0.346 rows=0 loops=1)

  • Index Cond: (id_block_to = 1)
23. 0.005 0.005 ↓ 1.2 5 1

Bitmap Index Scan on user_block_id_block_from_idx (cost=0.00..4.32 rows=4 width=0) (actual time=0.005..0.005 rows=5 loops=1)

  • Index Cond: (id_block_from = 1)