explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V3l9

Settings
# exclusive inclusive rows x rows loops node
1. 317.084 2,390.904 ↓ 51.0 51 1

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

  • Buffers: shared hit=25 read=476282, temp read=4231 written=4264
  • Functions: 94
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.524 ms, Inlining 62.200 ms, Optimization 348.501 ms, Emission 236.763 ms, Total 652.989 ms
2. 4.056 2,073.820 ↓ 51.0 51 1

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

  • Buffers: shared hit=25 read=476282, temp read=4231 written=4264
3. 2.785 2,069.764 ↓ 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,063.816..2,069.764 rows=24,993 loops=1)

  • Buffers: shared hit=25 read=476282, temp read=4231 written=4264
4. 12.652 2,066.979 ↓ 24,993.0 24,993 1

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

  • Sort Key: outfit.outfit_post_time DESC
  • Sort Method: external merge Disk: 2600kB
  • Buffers: shared hit=25 read=476282, temp read=4231 written=4264
5. 8.083 2,054.327 ↓ 24,993.0 24,993 1

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

  • Merge Cond: (outfit_comment.id_outfit = outfit.id_outfit)
  • Buffers: shared hit=25 read=476282, temp read=3906 written=3939
6. 48.727 2,003.281 ↑ 24.3 24,993 1

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

  • Buffers: shared hit=23 read=473963, temp read=3581 written=3614
7. 214.309 1,954.554 ↓ 1.0 612,110 1

Sort (cost=1,636,427.22..1,637,948.25 rows=608,412 width=16) (actual time=1,882.062..1,954.554 rows=612,110 loops=1)

  • Sort Key: outfit_comment.id_outfit, rel_outfit_comment_hashtag.id_hashtag
  • Sort Method: external merge Disk: 15616kB
  • Buffers: shared hit=23 read=473963, temp read=3581 written=3614
8. 5.840 1,740.245 ↓ 1.0 612,110 1

Gather (cost=10,290.27..1,567,576.49 rows=608,412 width=16) (actual time=62.354..1,740.245 rows=612,110 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=23 read=473963, temp read=1629 written=1652
9. 18.554 1,734.405 ↑ 3.0 204,037 3 / 3

Parallel Append (cost=9,290.27..1,505,735.29 rows=608,412 width=16) (actual time=376.125..1,734.405 rows=204,037 loops=3)

  • Buffers: shared hit=23 read=473963, temp read=1629 written=1652
10. 22.266 307.090 ↓ 2.4 113,761 1 / 3

Parallel Hash Join (cost=16,824.45..263,304.05 rows=46,962 width=16) (actual time=876.724..921.270 rows=113,761 loops=1)

  • Hash Cond: (rel_outfit_comment_hashtag.id_outfit_comment = outfit_comment.id_outfit_comment)
  • Buffers: shared hit=23 read=97377, temp read=1629 written=1652
11. 193.075 198.004 ↓ 2.4 113,761 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=22.815..594.011 rows=113,761 loops=1)

  • Recheck Cond: (id_hashtag = 4)
  • Rows Removed by Index Recheck: 3945070
  • Filter: (deleted_at IS NULL)
  • Buffers: shared hit=3 read=85978
12. 4.928 4.928 ↓ 1.0 113,761 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.785..14.785 rows=113,761 loops=1)

  • Index Cond: (id_hashtag = 4)
  • Buffers: shared hit=3 read=316
13. 14.795 86.821 ↓ 2.4 290,815 1 / 3

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

  • Buckets: 131072 Batches: 8 Memory Usage: 2752kB
  • Buffers: shared hit=1 read=11391, temp written=1136
14. 72.026 72.026 ↓ 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.589..216.077 rows=290,815 loops=1)

  • Filter: (deleted_at IS NULL)
  • Buffers: shared hit=1 read=11391
15. 1,390.524 1,408.761 ↑ 1.2 166,116 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=83.898..1,408.761 rows=166,116 loops=3)

  • Recheck Cond: (id_hashtag = 4)
  • Rows Removed by Index Recheck: 13083873
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=12956 lossy=134268
  • Buffers: shared read=376586
16. 18.237 18.237 ↓ 1.0 498,349 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=54.709..54.710 rows=498,349 loops=1)

  • Index Cond: (id_hashtag = 4)
  • Buffers: shared read=1366
17. 3.756 42.963 ↓ 24,995.0 24,995 1

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

  • Buffers: shared hit=2 read=2319, temp read=325 written=325
18. 12.115 39.207 ↓ 24,995.0 24,995 1

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

  • Sort Key: outfit.id_outfit
  • Sort Method: external merge Disk: 2600kB
  • Buffers: shared hit=2 read=2319, temp read=325 written=325
19. 19.696 27.092 ↓ 24,996.0 24,996 1

Nested Loop Left Join (cost=583.36..8,712.37 rows=1 width=96) (actual time=0.527..27.092 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)
  • Buffers: shared hit=2 read=2319
20. 7.396 7.396 ↑ 1.0 24,996 1

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

  • Filter: ((deleted_at IS NULL) AND (NOT private_flag))
  • Buffers: shared read=2217
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)

  • Buffers: shared hit=2 read=102
22. 0.024 0.506 ↑ 2.0 5 1

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

  • Recheck Cond: ((id_block_to = 1) OR (id_block_from = 1))
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=5
  • Buffers: shared hit=2 read=102
23. 0.001 0.482 ↓ 0.0 0 1

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

  • Buffers: shared hit=2 read=97
24. 0.470 0.470 ↓ 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.470..0.470 rows=0 loops=1)

  • Index Cond: (id_block_to = 1)
  • Buffers: shared read=97
25. 0.011 0.011 ↓ 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.011..0.011 rows=5 loops=1)

  • Index Cond: (id_block_from = 1)
  • Buffers: shared hit=2
Execution time : 2,396.667 ms