explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZE4i

Settings
# exclusive inclusive rows x rows loops node
1. 0.491 994.941 ↑ 56.4 543 1

Sort (cost=849,660.43..849,736.95 rows=30,609 width=137) (actual time=994.920..994.941 rows=543 loops=1)

  • Sort Key: ranked_messages.max_rank_value DESC, ranked_messages.rank_value DESC
  • Sort Method: quicksort Memory: 203kB
  • Functions: 39
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 13.742 ms, Inlining 30.437 ms, Optimization 476.560 ms, Emission 277.775 ms, Total 798.513 ms
2. 11.753 994.450 ↑ 56.4 543 1

Subquery Scan on ranked_messages (cost=811,566.97..847,379.80 rows=30,609 width=137) (actual time=966.023..994.450 rows=543 loops=1)

  • Filter: (ranked_messages.rank <= 4)
  • Rows Removed by Filter: 18313
3. 15.384 982.697 ↑ 4.9 18,856 1

WindowAgg (cost=811,566.97..830,162.14 rows=91,828 width=196) (actual time=965.953..982.697 rows=18,856 loops=1)

4. 19.998 967.313 ↑ 4.9 18,856 1

Sort (cost=811,566.97..811,796.54 rows=91,828 width=168) (actual time=965.925..967.313 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. 42.277 947.315 ↑ 4.9 18,856 1

Hash Join (cost=2,369.29..803,997.29 rows=91,828 width=168) (actual time=801.525..947.315 rows=18,856 loops=1)

  • Hash Cond: (buffer.networkid = network.networkid)
6. 19.289 119.704 ↑ 4.9 18,856 1

Nested Loop (cost=2,361.60..799,334.87 rows=91,828 width=155) (actual time=16.164..119.704 rows=18,856 loops=1)

7. 6.711 62.703 ↑ 4.9 18,856 1

Hash Join (cost=2,361.17..376,519.95 rows=91,828 width=116) (actual time=16.143..62.703 rows=18,856 loops=1)

  • Hash Cond: (backlog.bufferid = buffer.bufferid)
8. 3.552 55.744 ↑ 3.8 28,774 1

Nested Loop (cost=2,176.29..376,036.70 rows=108,425 width=103) (actual time=15.880..55.744 rows=28,774 loops=1)

9. 0.009 0.009 ↑ 1.0 1 1

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

10. 39.478 52.183 ↑ 3.8 28,774 1

Bitmap Heap Scan on backlog (cost=2,176.29..365,194.10 rows=108,425 width=189) (actual time=15.864..52.183 rows=28,774 loops=1)

  • Recheck Cond: ((tsv @@ query.query) AND ((type & 23559) > 0))
  • Heap Blocks: exact=23299
11. 12.705 12.705 ↑ 3.8 28,774 1

Bitmap Index Scan on backlog_tsv_filtered_idx (cost=0.00..2,149.19 rows=108,425 width=0) (actual time=12.705..12.705 rows=28,774 loops=1)

  • Index Cond: (tsv @@ query.query)
12. 0.102 0.248 ↑ 1.0 675 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
13. 0.146 0.146 ↑ 1.0 675 1

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

  • Filter: (userid = 2)
  • Rows Removed by Filter: 122
14. 37.712 37.712 ↑ 1.0 1 18,856

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

  • Index Cond: (senderid = backlog.senderid)
15. 0.016 785.334 ↑ 1.0 33 1

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

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

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

Execution time : 1,009.659 ms