pg_9_4_Slow QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=51055.44..51055.50 rows=25 width=268) (actual time=2746.814..2746.819 rows=25 loops=1) -> Sort (cost=51055.44..51271.92 rows=86592 width=268) (actual time=2746.812..2746.813 rows=25 loops=1) Sort Key: wo.workorderid Sort Method: top-N heapsort Memory: 31kB -> Hash Left Join (cost=2800.05..48611.88 rows=86592 width=268) (actual time=49.285..2634.867 rows=86130 loops=1) Hash Cond: (wo.oboid = sduser_onbehalfof.userid) -> Hash Left Join (cost=2120.45..47174.59 rows=86592 width=275) (actual time=36.652..2574.275 rows=86130 loops=1) Hash Cond: (wo.requesterid = sduser.userid) -> Nested Loop Left Join (cost=1440.86..44871.40 rows=86592 width=282) (actual time=23.899..2449.283 rows=86130 loops=1) Join Filter: (wos.statusid = std.statusid) -> Hash Left Join (cost=1440.86..43571.33 rows=86592 width=273) (actual time=23.889..2372.958 rows=86130 loops=1) Hash Cond: (wos.priorityid = pd.priorityid) -> Hash Left Join (cost=1439.52..42528.00 rows=86592 width=253) (actual time=23.848..2323.138 rows=86130 loops=1) Hash Cond: (wo.requesterid = aau.user_id) -> Hash Left Join (cost=779.93..40244.81 rows=86592 width=234) (actual time=11.128..2198.954 rows=86130 loops=1) Hash Cond: (wos.ownerid = ti.user_id) -> Hash Left Join (cost=120.34..38058.72 rows=86592 width=223) (actual time=0.280..2106.865 rows=86130 loops=1) Hash Cond: (wo.workorderid = wo_queue.workorderid) -> Hash Left Join (cost=117.66..37731.31 rows=86592 width=208) (actual time=0.239..2060.880 rows=86130 loops=1) Hash Cond: (wos.categoryid = cd.categoryid) -> Hash Left Join (cost=116.37..36687.54 rows=86592 width=205) (actual time=0.218..2013.580 rows=86130 loops=1) Hash Cond: (wo.siteid = sitedefinition.siteid) -> Merge Left Join (cost=108.95..36355.39 rows=86592 width=205) (actual time=0.041..1974.608 rows=86130 loops=1) Merge Cond: (wo.workorderid = wtp.workorderid) -> Merge Join (cost=4.12..36022.21 rows=86592 width=205) (actual time=0.032..1940.839 rows=86130 loops=1) Merge Cond: (wo.workorderid = wos.workorderid) -> Index Scan Backward using workorder_pk on workorder wo (cost=0.42..18645.63 rows=302945 width=157) (actual time=0.015..1020.405 rows=302945 loops=1) Filter: isparent -> Index Scan Backward using workorderstates_fk1_idx on workorderstates wos (cost=0.42..15538.74 rows=86592 width=136) (actual time=0.012..765.145 rows=86130 loops=1) Filter: (statusid = 1) Rows Removed by Filter: 216815 -> Sort (cost=104.83..108.61 rows=1510 width=8) (actual time=0.008..0.008 rows=0 loops=1) Sort Key: wtp.workorderid Sort Method: quicksort Memory: 25kB -> Seq Scan on wotoprojects wtp (cost=0.00..25.10 rows=1510 width=8) (actual time=0.000..0.000 rows=0 loops=1) -> Hash (cost=6.27..6.27 rows=92 width=16) (actual time=0.165..0.165 rows=92 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 5kB -> Hash Right Join (cost=3.07..6.27 rows=92 width=16) (actual time=0.072..0.128 rows=92 loops=1) Hash Cond: (sdorganization.org_id = sitedefinition.siteid) -> Seq Scan on sdorganization (cost=0.00..1.93 rows=93 width=16) (actual time=0.005..0.012 rows=93 loops=1) -> Hash (cost=1.92..1.92 rows=92 width=8) (actual time=0.055..0.055 rows=92 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 4kB -> Seq Scan on sitedefinition (cost=0.00..1.92 rows=92 width=8) (actual time=0.004..0.019 rows=92 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=19) (actual time=0.013..0.013 rows=13 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on categorydefinition cd (cost=0.00..1.13 rows=13 width=19) (actual time=0.003..0.006 rows=13 loops=1) -> Hash (cost=2.67..2.67 rows=1 width=23) (actual time=0.033..0.033 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Hash Right Join (cost=1.02..2.67 rows=1 width=23) (actual time=0.016..0.032 rows=1 loops=1) Hash Cond: (qd.queueid = wo_queue.queueid) -> Seq Scan on queuedefinition qd (cost=0.00..1.46 rows=46 width=23) (actual time=0.004..0.007 rows=46 loops=1) -> Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on workorder_queue wo_queue (cost=0.00..1.01 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1) -> Hash (cost=400.93..400.93 rows=20693 width=19) (actual time=10.824..10.824 rows=20693 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 1132kB -> Seq Scan on aaauser ti (cost=0.00..400.93 rows=20693 width=19) (actual time=0.006..4.313 rows=20693 loops=1) -> Hash (cost=400.93..400.93 rows=20693 width=19) (actual time=12.689..12.689 rows=20693 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 1040kB -> Seq Scan on aaauser aau (cost=0.00..400.93 rows=20693 width=19) (actual time=0.005..5.184 rows=20693 loops=1) -> Hash (cost=1.15..1.15 rows=15 width=20) (actual time=0.027..0.027 rows=15 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on prioritydefinition pd (cost=0.00..1.15 rows=15 width=20) (actual time=0.017..0.021 rows=15 loops=1) -> Materialize (cost=0.00..1.19 rows=1 width=17) (actual time=0.000..0.000 rows=1 loops=86130) -> Seq Scan on statusdefinition std (cost=0.00..1.19 rows=1 width=17) (actual time=0.004..0.008 rows=1 loops=1) Filter: (statusid = 1) Rows Removed by Filter: 14 -> Hash (cost=420.93..420.93 rows=20693 width=9) (actual time=12.726..12.726 rows=20693 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 970kB -> Seq Scan on sduser (cost=0.00..420.93 rows=20693 width=9) (actual time=0.006..5.942 rows=20693 loops=1) -> Hash (cost=420.93..420.93 rows=20693 width=9) (actual time=12.584..12.584 rows=20693 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 970kB -> Seq Scan on sduser sduser_onbehalfof (cost=0.00..420.93 rows=20693 width=9) (actual time=0.005..5.884 rows=20693 loops=1) Planning time: 69.901 ms Execution time: 2749.108 ms (75 rows)