explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1gZp

Settings
# exclusive inclusive rows x rows loops node
1. 0.529 239.401 ↑ 20.4 543 1

Sort (cost=345,191.04..345,218.77 rows=11,093 width=137) (actual time=239.381..239.401 rows=543 loops=1)

  • Sort Key: ranked_messages.max_rank_value DESC, ranked_messages.rank_value DESC
  • Sort Method: quicksort Memory: 203kB
  • Functions: 37
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 11.646 ms, Inlining 0.000 ms, Optimization 1.340 ms, Emission 36.061 ms, Total 49.047 ms
2. 12.685 238.872 ↑ 20.4 543 1

Subquery Scan on ranked_messages (cost=331,466.62..344,445.73 rows=11,093 width=137) (actual time=205.531..238.872 rows=543 loops=1)

  • Filter: (ranked_messages.rank <= 4)
  • Rows Removed by Filter: 18313
3. 19.127 226.187 ↑ 1.8 18,856 1

WindowAgg (cost=331,466.62..338,205.82 rows=33,280 width=196) (actual time=205.451..226.187 rows=18,856 loops=1)

4. 19.768 207.060 ↑ 1.8 18,856 1

Sort (cost=331,466.62..331,549.82 rows=33,280 width=168) (actual time=205.402..207.060 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: 10251kB
5. 47.967 187.292 ↑ 1.8 18,856 1

Nested Loop (cost=1,784.93..328,966.90 rows=33,280 width=168) (actual time=54.092..187.292 rows=18,856 loops=1)

6. 6.312 101.613 ↑ 1.8 18,856 1

Hash Join (cost=1,784.50..149,332.53 rows=33,280 width=121) (actual time=54.055..101.613 rows=18,856 loops=1)

  • Hash Cond: (buffer.networkid = network.networkid)
7. 8.442 57.343 ↑ 1.8 18,856 1

Hash Join (cost=1,773.42..145,868.65 rows=33,280 width=84) (actual time=16.089..57.343 rows=18,856 loops=1)

  • Hash Cond: (backlog.bufferid = buffer.bufferid)
8. 35.870 48.602 ↑ 1.4 28,774 1

Bitmap Heap Scan on backlog (cost=1,588.54..145,575.66 rows=39,295 width=71) (actual time=15.770..48.602 rows=28,774 loops=1)

  • Recheck Cond: ((tsv @@ '''star'' | ''star'' | ''star'' | ''star'''::tsquery) AND ((type & 23559) > 0))
  • Heap Blocks: exact=23299
9. 12.732 12.732 ↑ 1.4 28,774 1

Bitmap Index Scan on backlog_tsv_filtered_idx (cost=0.00..1,578.72 rows=39,295 width=0) (actual time=12.732..12.732 rows=28,774 loops=1)

  • Index Cond: (tsv @@ '''star'' | ''star'' | ''star'' | ''star'''::tsquery)
10. 0.114 0.299 ↑ 1.0 675 1

Hash (cost=115.69..115.69 rows=675 width=17) (actual time=0.299..0.299 rows=675 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
11. 0.185 0.185 ↑ 1.0 675 1

Seq Scan on buffer (cost=0.00..115.69 rows=675 width=17) (actual time=0.008..0.185 rows=675 loops=1)

  • Filter: (userid = 2)
  • Rows Removed by Filter: 122
12. 0.019 37.958 ↑ 1.0 33 1

Hash (cost=7.70..7.70 rows=33 width=45) (actual time=37.958..37.958 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.012 37.939 ↑ 1.0 33 1

Nested Loop (cost=0.00..7.70 rows=33 width=45) (actual time=37.922..37.939 rows=33 loops=1)

14. 37.901 37.901 ↑ 1.0 1 1

Function Scan on query (cost=0.00..0.10 rows=1 width=32) (actual time=37.900..37.901 rows=1 loops=1)

15. 0.026 0.026 ↑ 1.0 33 1

Seq Scan on network (cost=0.00..4.30 rows=33 width=13) (actual time=0.015..0.026 rows=33 loops=1)

16. 37.712 37.712 ↑ 1.0 1 18,856

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

  • Index Cond: (senderid = backlog.senderid)
Execution time : 251.999 ms