explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QLSi

Settings
# exclusive inclusive rows x rows loops node
1. 0.569 3,258.475 ↓ 38.8 543 1

Sort (cost=1,727.52..1,727.55 rows=14 width=137) (actual time=3,258.454..3,258.475 rows=543 loops=1)

  • Sort Key: ranked_messages.max_rank_value DESC, ranked_messages.rank_value DESC
  • Sort Method: quicksort Memory: 203kB
2. 12.981 3,257.906 ↓ 38.8 543 1

Subquery Scan on ranked_messages (cost=1,710.87..1,727.25 rows=14 width=137) (actual time=3,224.384..3,257.906 rows=543 loops=1)

  • Filter: (ranked_messages.rank <= 4)
  • Rows Removed by Filter: 18313
3. 19.192 3,244.925 ↓ 449.0 18,856 1

WindowAgg (cost=1,710.87..1,719.38 rows=42 width=196) (actual time=3,224.275..3,244.925 rows=18,856 loops=1)

4. 27.100 3,225.733 ↓ 449.0 18,856 1

Sort (cost=1,710.87..1,710.98 rows=42 width=168) (actual time=3,224.252..3,225.733 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: 7236kB
5. 64.292 3,198.633 ↓ 449.0 18,856 1

Hash Join (cost=1,264.09..1,709.74 rows=42 width=168) (actual time=1,916.768..3,198.633 rows=18,856 loops=1)

  • Hash Cond: (buffer.networkid = network.networkid)
6. 20.233 3,134.307 ↓ 449.0 18,856 1

Nested Loop (cost=1,256.41..1,699.96 rows=42 width=155) (actual time=1,916.700..3,134.307 rows=18,856 loops=1)

7. 9.189 1,926.146 ↓ 449.0 18,856 1

Hash Join (cost=1,255.98..1,418.80 rows=42 width=116) (actual time=1,916.662..1,926.146 rows=18,856 loops=1)

  • Hash Cond: (buffer.bufferid = backlog.bufferid)
8. 0.316 0.316 ↑ 1.0 675 1

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

  • Filter: (userid = 2)
  • Rows Removed by Filter: 122
9. 17.659 1,916.641 ↓ 587.2 28,774 1

Hash (cost=1,250.96..1,250.96 rows=49 width=103) (actual time=1,916.641..1,916.641 rows=28,774 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5443kB
10. 6.048 1,898.982 ↓ 587.2 28,774 1

Nested Loop (cost=645.49..1,250.96 rows=49 width=103) (actual time=16.917..1,898.982 rows=28,774 loops=1)

11. 0.006 0.006 ↑ 1.0 1 1

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

12. 1,879.042 1,892.928 ↓ 587.2 28,774 1

Bitmap Heap Scan on backlog (cost=645.49..1,245.96 rows=49 width=189) (actual time=16.907..1,892.928 rows=28,774 loops=1)

  • Recheck Cond: ((tsv @@ query.query) AND (tsv @@ '''star'''::tsquery))
  • Filter: ((type & 23559) > 0)
  • Rows Removed by Filter: 562
  • Heap Blocks: exact=23845
13. 13.886 13.886 ↓ 199.6 29,336 1

Bitmap Index Scan on backlog_tsv_idx (cost=0.00..645.47 rows=147 width=0) (actual time=13.886..13.886 rows=29,336 loops=1)

  • Index Cond: ((tsv @@ query.query) AND (tsv @@ '''star'''::tsquery))
14. 1,187.928 1,187.928 ↑ 1.0 1 18,856

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

  • Index Cond: (senderid = backlog.senderid)
15. 0.014 0.034 ↑ 1.0 33 1

Hash (cost=4.30..4.30 rows=33 width=13) (actual time=0.034..0.034 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
16. 0.020 0.020 ↑ 1.0 33 1

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

Planning time : 2.824 ms
Execution time : 3,259.419 ms