explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vc4c

Settings
# exclusive inclusive rows x rows loops node
1. 312.213 2,338.133 ↓ 51.0 51 1

Limit (cost=1,657,308.22..1,657,308.25 rows=1 width=96) (actual time=2,334.265..2,338.133 rows=51 loops=1)

2. 3.893 2,025.920 ↓ 51.0 51 1

WindowAgg (cost=1,657,308.22..1,657,308.25 rows=1 width=96) (actual time=2,025.913..2,025.920 rows=51 loops=1)

3. 2.681 2,022.027 ↓ 24,993.0 24,993 1

Subquery Scan on n (cost=1,657,308.22..1,657,308.23 rows=1 width=88) (actual time=2,016.398..2,022.027 rows=24,993 loops=1)

4. 12.334 2,019.346 ↓ 24,993.0 24,993 1

Sort (cost=1,657,308.22..1,657,308.22 rows=1 width=96) (actual time=2,016.389..2,019.346 rows=24,993 loops=1)

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

Merge Join (cost=1,645,139.60..1,657,308.21 rows=1 width=96) (actual time=1,883.629..2,007.012 rows=24,993 loops=1)

  • Merge Cond: (outfit_comment.id_outfit = outfit.id_outfit)
6. 44.728 1,958.798 ↑ 24.3 24,993 1

Unique (cost=1,636,427.22..1,640,990.31 rows=608,412 width=16) (actual time=1,849.002..1,958.798 rows=24,993 loops=1)

7. 200.384 1,914.070 ↓ 1.0 611,744 1

Sort (cost=1,636,427.22..1,637,948.25 rows=608,412 width=16) (actual time=1,849.001..1,914.070 rows=611,744 loops=1)

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

Gather (cost=10,290.27..1,567,576.49 rows=608,412 width=16) (actual time=63.620..1,713.686 rows=611,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 17.078 1,701.386 ↑ 3.0 203,915 3 / 3

Parallel Append (cost=9,290.27..1,505,735.29 rows=608,412 width=16) (actual time=372.191..1,701.386 rows=203,915 loops=3)

10. 21.675 305.025 ↓ 2.4 113,273 1 / 3

Parallel Hash Join (cost=16,824.45..263,304.05 rows=46,962 width=16) (actual time=870.596..915.074 rows=113,273 loops=1)

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

Parallel Bitmap Heap Scan on rel_outfit_comment_hashtag (cost=2,114.06..247,418.38 rows=46,962 width=16) (actual time=21.954..591.121 rows=113,273 loops=1)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 3942996
  • Filter: (deleted_at IS NULL)
12. 4.776 4.776 ↓ 1.0 113,273 1 / 3

Bitmap Index Scan on rel_outfit_comment_hashtag_id_hashtag_idx (cost=0.00..2,085.88 rows=112,709 width=0) (actual time=14.328..14.328 rows=113,273 loops=1)

  • Index Cond: (id_hashtag = 120)
13. 14.132 86.310 ↓ 2.4 290,815 1 / 3

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

  • Buckets: 131072 Batches: 8 Memory Usage: 2752kB
14. 72.178 72.178 ↓ 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=165.407..216.534 rows=290,815 loops=1)

  • Filter: (deleted_at IS NULL)
15. 1,360.543 1,379.283 ↑ 1.2 166,157 3 / 3

Parallel Bitmap Heap Scan on rel_outfit_hashtag (cost=9,290.27..1,233,305.06 rows=206,543 width=16) (actual time=82.010..1,379.283 rows=166,157 loops=3)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 13084002
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=12496 lossy=130694
16. 18.740 18.740 ↓ 1.0 498,471 1 / 3

Bitmap Index Scan on rel_outfit_hashtag_id_hashtag_idx (cost=0.00..9,166.34 rows=495,703 width=0) (actual time=56.221..56.221 rows=498,471 loops=1)

  • Index Cond: (id_hashtag = 120)
17. 3.417 40.747 ↓ 24,995.0 24,995 1

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

18. 11.716 37.330 ↓ 24,995.0 24,995 1

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

  • Sort Key: outfit.id_outfit
  • Sort Method: external merge Disk: 2600kB
19. 18.235 25.614 ↓ 24,996.0 24,996 1

Nested Loop Left Join (cost=583.36..8,712.37 rows=1 width=96) (actual time=0.508..25.614 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)
20. 7.379 7.379 ↑ 1.0 24,996 1

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

  • Filter: ((deleted_at IS NULL) AND (NOT private_flag))
21. 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)

22. 0.022 0.487 ↑ 2.0 5 1

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

  • Recheck Cond: ((id_block_to = 1) OR (id_block_from = 1))
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=5
23. 0.000 0.465 ↓ 0.0 0 1

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

24. 0.459 0.459 ↓ 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.459..0.459 rows=0 loops=1)

  • Index Cond: (id_block_to = 1)
25. 0.006 0.006 ↓ 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.006..0.006 rows=5 loops=1)

  • Index Cond: (id_block_from = 1)