explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6iZ8

Settings
# exclusive inclusive rows x rows loops node
1. 323.232 32,843.042 ↓ 51.0 51 1

Limit (cost=1,657,308.22..1,657,308.25 rows=1 width=96) (actual time=32,839.697..32,843.042 rows=51 loops=1)

  • Buffers: shared hit=27 read=475253, temp read=4220 written=4254
2. 4.794 32,519.810 ↓ 51.0 51 1

WindowAgg (cost=1,657,308.22..1,657,308.25 rows=1 width=96) (actual time=32,519.803..32,519.810 rows=51 loops=1)

  • Buffers: shared hit=27 read=475253, temp read=4220 written=4254
3. 2.628 32,515.016 ↓ 24,996.0 24,996 1

Subquery Scan on n (cost=1,657,308.22..1,657,308.23 rows=1 width=88) (actual time=32,509.322..32,515.016 rows=24,996 loops=1)

  • Buffers: shared hit=27 read=475253, temp read=4220 written=4254
4. 12.697 32,512.388 ↓ 24,996.0 24,996 1

Sort (cost=1,657,308.22..1,657,308.22 rows=1 width=96) (actual time=32,509.314..32,512.388 rows=24,996 loops=1)

  • Sort Key: outfit.outfit_post_time DESC
  • Sort Method: external merge Disk: 2600kB
  • Buffers: shared hit=27 read=475253, temp read=4220 written=4254
5. 7.797 32,499.691 ↓ 24,996.0 24,996 1

Merge Join (cost=1,645,139.60..1,657,308.21 rows=1 width=96) (actual time=32,368.222..32,499.691 rows=24,996 loops=1)

  • Merge Cond: (outfit_comment.id_outfit = outfit.id_outfit)
  • Buffers: shared hit=27 read=475253, temp read=3895 written=3929
6. 47.956 32,445.942 ↑ 24.3 24,996 1

Unique (cost=1,636,427.22..1,640,990.31 rows=608,412 width=16) (actual time=32,328.899..32,445.942 rows=24,996 loops=1)

  • Buffers: shared hit=25 read=472934, temp read=3570 written=3604
7. 350.032 32,397.986 ↓ 1.0 610,267 1

Sort (cost=1,636,427.22..1,637,948.25 rows=608,412 width=16) (actual time=32,328.898..32,397.986 rows=610,267 loops=1)

  • Sort Key: outfit_comment.id_outfit, rel_outfit_comment_hashtag.id_hashtag
  • Sort Method: external merge Disk: 15568kB
  • Buffers: shared hit=25 read=472934, temp read=3570 written=3604
8. 0.768 32,047.954 ↓ 1.0 610,267 1

Gather (cost=10,290.27..1,567,576.49 rows=608,412 width=16) (actual time=67.372..32,047.954 rows=610,267 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=25 read=472934, temp read=1624 written=1648
9. 51.149 32,047.186 ↑ 3.0 203,422 3 / 3

Parallel Append (cost=9,290.27..1,505,735.29 rows=608,412 width=16) (actual time=6,121.952..32,047.186 rows=203,422 loops=3)

  • Buffers: shared hit=25 read=472934, temp read=1624 written=1648
10. 46.617 6,051.794 ↓ 2.4 112,918 1 / 3

Parallel Hash Join (cost=16,824.45..263,304.05 rows=46,962 width=16) (actual time=18,115.709..18,155.381 rows=112,918 loops=1)

  • Hash Cond: (rel_outfit_comment_hashtag.id_outfit_comment = outfit_comment.id_outfit_comment)
  • Buffers: shared hit=25 read=96829, temp read=1624 written=1648
11. 5,912.507 5,919.706 ↓ 2.4 112,918 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=28.837..17,759.118 rows=112,918 loops=1)

  • Recheck Cond: (id_hashtag = 5)
  • Rows Removed by Index Recheck: 3943485
  • Filter: (deleted_at IS NULL)
  • Buffers: shared hit=4 read=85431
12. 7.199 7.199 ↓ 1.0 112,918 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=21.598..21.598 rows=112,918 loops=1)

  • Index Cond: (id_hashtag = 5)
  • Buffers: shared hit=3 read=313
13. 14.445 85.471 ↓ 2.4 290,815 1 / 3

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

  • Buckets: 131072 Batches: 8 Memory Usage: 2752kB
  • Buffers: shared hit=2 read=11390, temp written=1136
14. 71.026 71.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=164.610..213.078 rows=290,815 loops=1)

  • Filter: (deleted_at IS NULL)
  • Buffers: shared hit=2 read=11390
15. 25,924.650 25,944.243 ↑ 1.2 165,783 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.518..25,944.243 rows=165,783 loops=3)

  • Recheck Cond: (id_hashtag = 5)
  • Rows Removed by Index Recheck: 13084352
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=14560 lossy=139484
  • Buffers: shared read=376105
16. 19.593 19.593 ↓ 1.0 497,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=58.778..58.778 rows=497,349 loops=1)

  • Index Cond: (id_hashtag = 5)
  • Buffers: shared read=1363
17. 3.616 45.952 ↓ 24,996.0 24,996 1

Materialize (cost=8,712.38..8,712.39 rows=1 width=96) (actual time=39.303..45.952 rows=24,996 loops=1)

  • Buffers: shared hit=2 read=2319, temp read=325 written=325
18. 12.635 42.336 ↓ 24,996.0 24,996 1

Sort (cost=8,712.38..8,712.38 rows=1 width=96) (actual time=39.301..42.336 rows=24,996 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. 29.701 29.701 ↓ 24,996.0 24,996 1

Nested Loop Left Join (cost=583.36..8,712.37 rows=1 width=96) (actual time=0.568..29.701 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)