explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tLH4

Settings
# exclusive inclusive rows x rows loops node
1. 314.070 2,332.771 ↓ 51.0 51 1

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

  • Functions: 94
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.766 ms, Inlining 60.997 ms, Optimization 343.606 ms, Emission 234.149 ms, Total 645.518 ms
2. 3.832 2,018.701 ↓ 51.0 51 1

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

3. 2.587 2,014.869 ↓ 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,009.294..2,014.869 rows=24,993 loops=1)

4. 11.650 2,012.282 ↓ 24,993.0 24,993 1

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

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

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

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

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

7. 197.248 1,909.587 ↓ 1.0 611,744 1

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

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

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

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

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

10. 22.264 305.215 ↓ 2.4 113,273 1 / 3

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

  • Hash Cond: (rel_outfit_comment_hashtag.id_outfit_comment = outfit_comment.id_outfit_comment)
11. 191.867 196.841 ↓ 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=22.420..590.522 rows=113,273 loops=1)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 3942996
  • Filter: (deleted_at IS NULL)
12. 4.974 4.974 ↓ 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.922..14.922 rows=113,273 loops=1)

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

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

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

  • Filter: (deleted_at IS NULL)
15. 1,360.644 1,378.626 ↑ 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=81.566..1,378.626 rows=166,157 loops=3)

  • Recheck Cond: (id_hashtag = 120)
  • Rows Removed by Index Recheck: 13084002
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=12653 lossy=130635
16. 17.982 17.982 ↓ 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=53.945..53.945 rows=498,471 loops=1)

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

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

18. 9.996 35.833 ↓ 24,995.0 24,995 1

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

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

Nested Loop Left Join (cost=583.36..8,712.37 rows=1 width=96) (actual time=0.522..25.837 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.384 7.384 ↑ 1.0 24,996 1

Seq Scan on outfit (cost=0.00..2,466.96 rows=24,996 width=96) (actual time=0.013..7.384 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.023 0.501 ↑ 2.0 5 1

Bitmap Heap Scan on user_block (cost=583.36..621.28 rows=10 width=24) (actual time=0.486..0.501 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.478 ↓ 0.0 0 1

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

24. 0.472 0.472 ↓ 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.472..0.472 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)
Execution time : 2,339.760 ms