explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TKQ8

Settings
# exclusive inclusive rows x rows loops node
1. 312.632 3,560.020 ↓ 51.0 51 1

Limit (cost=1,517,637.56..1,517,637.59 rows=1 width=96) (actual time=3,555.543..3,560.020 rows=51 loops=1)

2. 155.283 3,247.388 ↓ 51.0 51 1

WindowAgg (cost=1,517,637.56..1,517,637.59 rows=1 width=96) (actual time=3,247.379..3,247.388 rows=51 loops=1)

3. 60.389 3,092.105 ↓ 611,744.0 611,744 1

Subquery Scan on n (cost=1,517,637.56..1,517,637.58 rows=1 width=88) (actual time=2,941.619..3,092.105 rows=611,744 loops=1)

4. 348.561 3,031.716 ↓ 611,744.0 611,744 1

Sort (cost=1,517,637.56..1,517,637.57 rows=1 width=96) (actual time=2,941.608..3,031.716 rows=611,744 loops=1)

  • Sort Key: outfit.outfit_post_time DESC
  • Sort Method: external merge Disk: 63,480kB
5. 6.781 2,683.155 ↓ 611,744.0 611,744 1

Gather (cost=19,002.65..1,517,637.55 rows=1 width=96) (actual time=240.421..2,683.155 rows=611,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 70.125 2,676.374 ↓ 203,915.0 203,915 3 / 3

Hash Join (cost=18,002.65..1,516,637.45 rows=1 width=96) (actual time=660.389..2,676.374 rows=203,915 loops=3)

  • Hash Cond: ("*SELECT* 2".id_outfit = outfit.id_outfit)
7. 18.150 2,362.951 ↑ 3.0 203,915 3 / 3

Parallel Append (cost=9,290.27..1,505,643.29 rows=608,412 width=8) (actual time=417.053..2,362.951 rows=203,915 loops=3)

8. 3.608 366.256 ↓ 1.0 113,273 1 / 3

Subquery Scan on *SELECT* 2 (cost=16,824.45..264,339.14 rows=112,709 width=8) (actual time=1,039.083..1,098.768 rows=113,273 loops=1)

9. 22.571 362.648 ↓ 2.4 113,273 1 / 3

Parallel Hash Join (cost=16,824.45..263,212.05 rows=46,962 width=16) (actual time=1,039.081..1,087.944 rows=113,273 loops=1)

  • Hash Cond: (rel_outfit_comment_hashtag.id_outfit_comment = outfit_comment.id_outfit_comment)
10. 275.982 292.562 ↓ 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=8) (actual time=73.349..877.686 rows=113,273 loops=1)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 3,942,996
  • Filter: (deleted_at IS NULL)
11. 16.580 16.580 ↓ 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=49.739..49.739 rows=113,273 loops=1)

  • Index Cond: (id_hashtag = 120)
12. 14.665 47.515 ↓ 2.4 290,815 1 / 3

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,752kB
13. 32.850 32.850 ↓ 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=0.019..98.550 rows=290,815 loops=1)

  • Filter: (deleted_at IS NULL)
14. 20.581 1,978.545 ↑ 3.0 166,157 3 / 3

Subquery Scan on *SELECT* 1 (cost=9,290.27..1,238,262.09 rows=495,703 width=8) (actual time=70.708..1,978.545 rows=166,157 loops=3)

15. 1,889.829 1,957.964 ↑ 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=70.707..1,957.964 rows=166,157 loops=3)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 13,084,002
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=13,407 lossy=126,131
16. 68.135 68.135 ↓ 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=204.404..204.404 rows=498,471 loops=1)

  • Index Cond: (id_hashtag = 120)
17. 5.328 243.298 ↓ 24,996.0 24,996 3 / 3

Hash (cost=8,712.37..8,712.37 rows=1 width=96) (actual time=243.298..243.298 rows=24,996 loops=3)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,381kB
18. 18.568 237.970 ↓ 24,996.0 24,996 3 / 3

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

  • Join Filter: (((outfit.id_user_app = user_block.id_block_from) AND (user_block.id_block_to = 300)) OR ((outfit.id_user_app = user_block.id_block_to) AND (user_block.id_block_from = 3)))
  • Rows Removed by Join Filter: 124,980
  • Filter: (user_block.id_user_block IS NULL)
19. 219.402 219.402 ↑ 1.0 24,996 3 / 3

Seq Scan on outfit (cost=0.00..2,466.96 rows=24,996 width=96) (actual time=214.433..219.402 rows=24,996 loops=3)

  • Filter: ((deleted_at IS NULL) AND (NOT private_flag))
20. 0.000 0.000 ↑ 2.0 5 74,988 / 3

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

21. 0.019 0.422 ↑ 2.0 5 3 / 3

Bitmap Heap Scan on user_block (cost=583.36..621.28 rows=10 width=24) (actual time=0.408..0.422 rows=5 loops=3)

  • Recheck Cond: ((id_block_to = 300) OR (id_block_from = 3))
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=5
22. 0.002 0.403 ↓ 0.0 0 3 / 3

BitmapOr (cost=583.36..583.36 rows=10 width=0) (actual time=0.402..0.403 rows=0 loops=3)

23. 0.384 0.384 ↓ 0.0 0 3 / 3

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

  • Index Cond: (id_block_to = 300)
24. 0.017 0.017 ↓ 1.2 5 3 / 3

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

  • Index Cond: (id_block_from = 3)