explain.depesz.com

PostgreSQL's explain analyze made readable

Result: coG0

Settings
# exclusive inclusive rows x rows loops node
1. 0.525 155.746 ↑ 5.1 543 1

Sort (cost=68,624.67..68,631.60 rows=2,775 width=137) (actual time=155.725..155.746 rows=543 loops=1)

  • Sort Key: ranked_messages.max_rank_value DESC, ranked_messages.rank_value DESC
  • Sort Method: quicksort Memory: 199kB
2. 11.975 155.221 ↑ 5.1 543 1

Subquery Scan on ranked_messages (cost=65,219.21..68,465.96 rows=2,775 width=137) (actual time=123.521..155.221 rows=543 loops=1)

  • Filter: (ranked_messages.rank <= 4)
  • Rows Removed by Filter: 18313
3. 18.203 143.246 ↓ 2.3 18,856 1

WindowAgg (cost=65,219.21..66,905.02 rows=8,325 width=196) (actual time=123.443..143.246 rows=18,856 loops=1)

4. 15.930 125.043 ↓ 2.3 18,856 1

Sort (cost=65,219.21..65,240.02 rows=8,325 width=168) (actual time=123.414..125.043 rows=18,856 loops=1)

  • Sort Key: backlog.bufferid, ((((CASE WHEN (backlog.type = ANY ('{1,4}'::integer[])) THEN 1.0 WHEN (backlog.type = ANY ('{2,1024,2048,4096,16384}'::integer[])) THEN 0.8 WHEN (backlog.type = ANY ('{32,64,128,256,512,32768,65536}'::integer[])) THEN 0.6 WHEN (backlog.type = ANY ('{8,16,8192,131072}'::integer[])) THEN 0.4 ELSE 0.2 END ^ '32'::numeric))::double precision * (('1'::double precision / (date_part('epoch'::text, CURRENT_TIMESTAMP) - date_part('epoch'::text, backlog."time"))) ^ '1'::double precision))) DESC
  • Sort Method: quicksort Memory: 7253kB
5. 36.512 109.113 ↓ 2.3 18,856 1

Gather (cost=2,444.58..64,677.12 rows=8,325 width=168) (actual time=16.516..109.113 rows=18,856 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 15.077 72.601 ↓ 1.8 6,285 3 / 3

Nested Loop (cost=1,444.58..62,844.62 rows=3,469 width=168) (actual time=5.491..72.601 rows=6,285 loops=3)

7. 2.293 32.383 ↓ 1.8 6,285 3 / 3

Hash Join (cost=1,444.15..39,466.46 rows=3,469 width=121) (actual time=5.431..32.383 rows=6,285 loops=3)

  • Hash Cond: (buffer.networkid = network.networkid)
8. 3.245 30.034 ↓ 1.8 6,285 3 / 3

Hash Join (cost=1,433.07..39,095.47 rows=3,469 width=84) (actual time=5.308..30.034 rows=6,285 loops=3)

  • Hash Cond: (backlog.bufferid = buffer.bufferid)
9. 22.801 26.398 ↓ 2.3 9,591 3 / 3

Parallel Bitmap Heap Scan on backlog (cost=1,248.19..38,899.33 rows=4,096 width=71) (actual time=4.903..26.398 rows=9,591 loops=3)

  • Recheck Cond: ((tsv @@ '''star'''::tsquery) AND ((type & 23559) > 0))
  • Heap Blocks: exact=12563
10. 3.597 3.597 ↓ 2.9 28,774 1 / 3

Bitmap Index Scan on backlog_tsv_filtered_idx (cost=0.00..1,245.73 rows=9,830 width=0) (actual time=10.790..10.790 rows=28,774 loops=1)

  • Index Cond: (tsv @@ '''star'''::tsquery)
11. 0.136 0.391 ↑ 1.0 675 3 / 3

Hash (cost=115.69..115.69 rows=675 width=17) (actual time=0.391..0.391 rows=675 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
12. 0.255 0.255 ↑ 1.0 675 3 / 3

Seq Scan on buffer (cost=0.00..115.69 rows=675 width=17) (actual time=0.010..0.255 rows=675 loops=3)

  • Filter: (userid = 2)
  • Rows Removed by Filter: 122
13. 0.015 0.056 ↑ 1.0 33 3 / 3

Hash (cost=7.70..7.70 rows=33 width=45) (actual time=0.056..0.056 rows=33 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.009 0.041 ↑ 1.0 33 3 / 3

Nested Loop (cost=0.00..7.70 rows=33 width=45) (actual time=0.031..0.041 rows=33 loops=3)

15. 0.014 0.014 ↑ 1.0 1 3 / 3

Function Scan on query (cost=0.00..0.10 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=3)

16. 0.018 0.018 ↑ 1.0 33 3 / 3

Seq Scan on network (cost=0.00..4.30 rows=33 width=13) (actual time=0.014..0.018 rows=33 loops=3)

17. 25.141 25.141 ↑ 1.0 1 18,856 / 3

Index Scan using sender_pkey on sender (cost=0.43..6.69 rows=1 width=55) (actual time=0.004..0.004 rows=1 loops=18,856)

  • Index Cond: (senderid = backlog.senderid)
Planning time : 3.159 ms
Execution time : 156.874 ms