explain.depesz.com

PostgreSQL's explain analyze made readable

Result: prrT

Settings
# exclusive inclusive rows x rows loops node
1. 0.732 56,075.411 ↑ 56.4 543 1

Sort (cost=765,625.09..765,701.61 rows=30,609 width=137) (actual time=56,075.379..56,075.411 rows=543 loops=1)

  • Sort Key: ranked_messages.max_rank_value DESC, ranked_messages.rank_value DESC
  • Sort Method: quicksort Memory: 203kB
  • Functions: 113
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 32.029 ms, Inlining 215.021 ms, Optimization 1534.449 ms, Emission 794.099 ms, Total 2575.597 ms
2. 14.930 56,074.679 ↑ 56.4 543 1

Subquery Scan on ranked_messages (cost=727,531.93..763,344.46 rows=30,609 width=137) (actual time=56,034.188..56,074.679 rows=543 loops=1)

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

WindowAgg (cost=727,531.93..746,126.90 rows=91,827 width=196) (actual time=56,034.079..56,059.749 rows=18,856 loops=1)

4. 18.910 56,036.598 ↑ 4.9 18,856 1

Sort (cost=727,531.93..727,761.50 rows=91,827 width=168) (actual time=56,034.033..56,036.598 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. 121.507 56,017.688 ↑ 4.9 18,856 1

Gather (cost=697,739.76..719,962.34 rows=91,827 width=168) (actual time=55,733.257..56,017.688 rows=18,856 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 12.853 55,896.181 ↑ 6.1 6,285 3 / 3

Hash Join (cost=696,739.76..709,779.64 rows=38,261 width=168) (actual time=55,663.532..55,896.181 rows=6,285 loops=3)

  • Hash Cond: (buffer.networkid = network.networkid)
7. 112.406 55,034.911 ↑ 6.1 6,285 3 / 3

Merge Join (cost=696,732.08..707,832.58 rows=38,261 width=155) (actual time=54,814.994..55,034.911 rows=6,285 loops=3)

  • Merge Cond: (sender.senderid = backlog.senderid)
8. 1,029.511 3,592.765 ↑ 1.3 1,079,633 3 / 3

Sort (cost=312,678.92..316,094.77 rows=1,366,340 width=55) (actual time=3,487.557..3,592.765 rows=1,079,633 loops=3)

  • Sort Key: sender.senderid
  • Sort Method: quicksort Memory: 135274kB
  • Worker 0: Sort Method: quicksort Memory: 193132kB
  • Worker 1: Sort Method: quicksort Memory: 182493kB
9. 2,563.254 2,563.254 ↑ 1.3 1,085,043 3 / 3

Parallel Seq Scan on sender (cost=0.00..173,436.00 rows=1,366,340 width=55) (actual time=0.017..2,563.254 rows=1,085,043 loops=3)

10. 78.575 51,329.740 ↑ 4.9 18,856 3 / 3

Sort (cost=384,050.05..384,279.61 rows=91,827 width=116) (actual time=51,327.299..51,329.740 rows=18,856 loops=3)

  • Sort Key: backlog.senderid
  • Sort Method: quicksort Memory: 6149kB
  • Worker 0: Sort Method: quicksort Memory: 6149kB
  • Worker 1: Sort Method: quicksort Memory: 6149kB
11. 19.901 51,251.165 ↑ 4.9 18,856 3 / 3

Hash Join (cost=2,325.17..376,480.46 rows=91,827 width=116) (actual time=11.810..51,251.165 rows=18,856 loops=3)

  • Hash Cond: (backlog.bufferid = buffer.bufferid)
12. 9.588 51,230.925 ↑ 3.8 28,774 3 / 3

Nested Loop (cost=2,140.29..375,997.21 rows=108,424 width=103) (actual time=11.457..51,230.925 rows=28,774 loops=3)

13. 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)

14. 51,213.251 51,221.323 ↑ 3.8 28,774 3 / 3

Bitmap Heap Scan on backlog (cost=2,140.29..365,154.71 rows=108,424 width=189) (actual time=11.435..51,221.323 rows=28,774 loops=3)

  • Recheck Cond: ((tsv @@ query.query) AND ((type & 23559) > 0))
  • Heap Blocks: exact=23299
15. 8.072 8.072 ↑ 3.8 28,774 3 / 3

Bitmap Index Scan on backlog_tsv_filtered_idx (cost=0.00..2,113.18 rows=108,424 width=0) (actual time=8.072..8.072 rows=28,774 loops=3)

  • Index Cond: (tsv @@ query.query)
16. 0.110 0.339 ↑ 1.0 675 3 / 3

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

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

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

  • Filter: (userid = 2)
  • Rows Removed by Filter: 122
18. 0.019 848.417 ↑ 1.0 33 3 / 3

Hash (cost=4.30..4.30 rows=33 width=13) (actual time=848.417..848.417 rows=33 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 848.398 848.398 ↑ 1.0 33 3 / 3

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

Execution time : 56,099.878 ms