explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Te6g : Optimization for: Optimization for: plan #ZBO7; plan #IF7Y

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 101.585 156.942 ↑ 1.0 31 1

Limit (cost=2,544,414.38..2,544,414.62 rows=31 width=16) (actual time=152.989..156.942 rows=31 loops=1)

  • Functions: 21
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 4.846 ms, Inlining 10.774 ms, Optimization 57.861 ms, Emission 32.811 ms, Total 106.292 ms
2. 0.028 55.357 ↑ 295.2 31 1

Unique (cost=2,544,414.38..2,544,483.01 rows=9,150 width=16) (actual time=51.410..55.357 rows=31 loops=1)

3. 0.099 55.329 ↑ 295.2 31 1

Sort (cost=2,544,414.38..2,544,437.26 rows=9,150 width=16) (actual time=51.408..55.329 rows=31 loops=1)

  • Sort Key: mentions.posted_at DESC, mentions.post_id
  • Sort Method: quicksort Memory: 34kB
4. 52.381 55.230 ↑ 47.4 193 1

Gather (cost=1,099.19..2,543,812.33 rows=9,150 width=16) (actual time=0.536..55.230 rows=193 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.000 2.849 ↑ 56.1 96 2 / 2

Nested Loop (cost=99.19..2,541,897.33 rows=5,382 width=16) (actual time=0.168..2.849 rows=96 loops=2)

6. 0.380 0.488 ↑ 5.8 266 2 / 2

Parallel Bitmap Heap Scan on research_outputs ro (cost=81.98..10,352.71 rows=1,541 width=8) (actual time=0.151..0.488 rows=266 loops=2)

  • Recheck Cond: (altmetric_jid = ANY ('{4f6fa62a3cf058f610007eea,4f6fa92b3cf058f61000ddc3,4f6fa8393cf058f61000be70}'::text[]))
  • Heap Blocks: exact=516
7. 0.108 0.108 ↑ 4.9 531 1 / 2

Bitmap Index Scan on research_outputs_1 (cost=0.00..81.33 rows=2,619 width=0) (actual time=0.215..0.216 rows=531 loops=1)

  • Index Cond: (altmetric_jid = ANY ('{4f6fa62a3cf058f610007eea,4f6fa92b3cf058f61000ddc3,4f6fa8393cf058f61000be70}'::text[]))
8. 1.327 2.389 ↓ 0.0 0 531 / 2

Bitmap Heap Scan on mentions (cost=17.20..1,637.81 rows=498 width=24) (actual time=0.008..0.009 rows=0 loops=531)

  • Recheck Cond: (research_output_id = ro.id)
  • Filter: (post_type = ANY ('{tweet,linkedin,pinterest,rdt,fbwall,weibo}'::text[]))
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=1,333
9. 1.062 1.062 ↑ 68.3 9 531 / 2

Bitmap Index Scan on mentions_constraint_0 (cost=0.00..17.08 rows=615 width=0) (actual time=0.004..0.004 rows=9 loops=531)

  • Index Cond: (research_output_id = ro.id)
Execution time : 159.869 ms