query-plan-1151

Change label
Query Plan as raw text
Summary
Time Node
Node (ms) Percent
6977.645 100.00% 
 Sort (cost=2306447.27..2308533.22 rows=834379 width=234) (actual time=6972.572..6977.645 rows=3002 loops=1)
   Sort Key: usr.usr_id
   Sort Method:  quicksort  Memory: 519kB
6964.486 99.81% 
   -> Hash Left Join (cost=20678.21..2130269.67 rows=834379 width=234) (actual time=6332.161..6964.486 rows=3002 loops=1)
         Hash Cond: (bucket.bucket_id = one.bucket_id)
6054.068 86.76% 
         -> GroupAggregate (cost=11535.98..2091204.89 rows=59941 width=56) (actual time=5433.873..6054.068 rows=3002 loops=1)
5480.375 78.54% 
               -> Sort (cost=11535.98..11685.83 rows=59941 width=56) (actual time=5395.874..5480.375 rows=48892 loops=1)
                     Sort Key: usr.usr_id, bucket.bucket_id, usr.displayname, bucket.name, usr.activeuser, usr.reports_to_id
                     Sort Method:  quicksort  Memory: 8290kB
4398.676 63.04% 
                     -> Merge Left Join (cost=5865.16..6779.28 rows=59941 width=56) (actual time=4127.949..4398.676 rows=48892 loops=1)
                           Merge Cond: (bucket.bucket_id = locate.bucket_id)
133.604 1.91% 
                           -> Sort (cost=534.65..542.16 rows=3002 width=48) (actual time=127.778..133.604 rows=3002 loops=1)
                                 Sort Key: bucket.bucket_id
                                 Sort Method:  quicksort  Memory: 452kB
120.201 1.72% 
                                 -> Hash Left Join (cost=83.64..361.26 rows=3002 width=48) (actual time=35.179..120.201 rows=3002 loops=1)
                                       Hash Cond: (usr.usr_id = bucket.user_id)
80.970 1.16% 
                                       -> Seq Scan on usr (cost=0.0..231.02 rows=3002 width=23) (actual time=9.091..80.970 rows=3002 loops=1)
26.053 0.37% 
                                       -> Hash (cost=48.84..48.84 rows=2784 width=29) (actual time=26.053..26.053 rows=2784 loops=1)
20.350 0.29% 
                                             -> Seq Scan on bucket (cost=0.0..48.84 rows=2784 width=29) (actual time=14.105..20.350 rows=2784 loops=1)
4088.469 58.59% 
                           -> Sort (cost=5330.5..5469.47 rows=55588 width=12) (actual time=4000.156..4088.469 rows=46722 loops=1)
                                 Sort Key: locate.bucket_id
                                 Sort Method:  quicksort  Memory: 3727kB
3895.183 55.82% 
                                 -> Index Scan using locate_open on locate (cost=0.0..949.48 rows=55588 width=12) (actual time=49.804..3895.183 rows=46722 loops=1)
                                       Index Cond: (open = true)
               SubPlan
300.195 4.30% 
                 -> Nested Loop (cost=0.0..26.39 rows=1 width=28) (actual time=0.105..0.105 rows=0 loops=2859)
168.681 2.42% 
                       -> Index Scan using usr_group_by_user on usr_group ug (cost=0.0..9.81 rows=2 width=8) (actual time=0.048..0.059 rows=2 loops=2859)
                             Index Cond: (usr_id = $0)
104.720 1.50% 
                       -> Index Scan using user_right_map_pkey on user_right_map urm (cost=0.0..8.27 rows=1 width=20) (actual time=0.020..0.020 rows=0 loops=5236)
                             Index Cond: (((urm.right_id)::text = 'MAN'::text) AND (urm.prin_id = ug.group_id))
102.068 1.46% 
                 -> Index Scan using user_right_map_pkey on user_right_map urm (cost=0.0..8.27 rows=1 width=20) (actual time=0.034..0.034 rows=0 loops=3002)
                       Index Cond: (((right_id)::text = 'MAN'::text) AND (prin_id = $0))
898.260 12.87% 
         -> Hash (cost=9107.43..9107.43 rows=2784 width=12) (actual time=898.260..898.260 rows=62 loops=1)
898.127 12.87% 
               -> Subquery Scan one (cost=9044.79..9107.43 rows=2784 width=12) (actual time=897.767..898.127 rows=62 loops=1)
897.912 12.87% 
                     -> HashAggregate (cost=9044.79..9079.59 rows=2784 width=8) (actual time=897.761..897.912 rows=62 loops=1)
889.275 12.74% 
                           -> Hash Join (cost=1916.88..8885.69 rows=31819 width=8) (actual time=221.651..889.275 rows=4090 loops=1)
                                 Hash Cond: (i.bucket_id = b.bucket_id)
863.663 12.38% 
                                 -> Hash Join (cost=1833.24..8285.0 rows=31819 width=8) (actual time=210.969..863.663 rows=4090 loops=1)
                                       Hash Cond: (iv.investigation_id = i.investigation_id)
675.437 9.68% 
                                       -> Bitmap Heap Scan on investigation_version iv (cost=739.57..6475.41 rows=31819 width=4) (actual time=43.882..675.437 rows=4090 loops=1)
                                             Recheck Cond: ((ending_date > now()) AND ((status)::text <> 'closed'::text))
30.955 0.44% 
                                             -> Bitmap Index Scan on investigation_version_open_by_ending_date (cost=0.0..731.62 rows=31819 width=0) (actual time=30.955..30.955 rows=4090 loops=1)
                                                   Index Cond: (ending_date > now())
167.041 2.39% 
                                       -> Hash (cost=703.85..703.85 rows=31185 width=8) (actual time=167.041..167.041 rows=31185 loops=1)
104.670 1.50% 
                                             -> Seq Scan on investigation i (cost=0.0..703.85 rows=31185 width=8) (actual time=12.326..104.670 rows=31185 loops=1)
10.666 0.15% 
                                 -> Hash (cost=48.84..48.84 rows=2784 width=4) (actual time=10.666..10.666 rows=2784 loops=1)
5.051 0.07% 
                                       -> Seq Scan on bucket b (cost=0.0..48.84 rows=2784 width=4) (actual time=0.012..5.051 rows=2784 loops=1)
Run-time
6.982829 seconds (6982.829 ms)