On Thu, Jan 31, 2013 at 10:24 PM, [email protected] <[email protected]> wrote: > On Thu, Jan 31, 2013 at 09:02:59PM +0400, Ruslan Zakirov wrote: >> On Thu, Jan 31, 2013 at 8:45 PM, [email protected] <[email protected]> wrote: >> > Dear RT community, >> > >> > While investigating performance issues caused by enabling the >> > option UseSQLForACLChecks, a big cause of the slowdown was drawing >> > the QuickSeach panel and its resulting DB queries. In RT4 they >> > have been consolidated into a single query. I have attached a >> > patched version of html/Elements/QueueSummary that does the same >> > for RT 3.8.x. The sample that was 15s before is now 2s. >> >> It should be even faster. Don't show inactive statuses if you have >> many historical tickets. An index may help. Also, 2 seconds can be >> result of hitting Pg problem I mentioned earlier. Explain of the query >> will answer this.
Explain quoted below looks good. Have you meant 2 seconds for the page or quick search box only? I think whole page and it sounds not bad. >> > Regards, >> > Ken >> >> -- >> Best regards, Ruslan. >> > > Hi Ruslan, > > Here is the EXPLAIN. It looks like a reasonable plan. > > Regards, > Ken > > > EXPLAIN ANALYZE SELECT COUNT(DISTINCT main.id) AS id, main.Status AS status, > main.Queue AS queue FROM Tickets main WHERE (main.Status != 'deleted') AND ( > ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) > AND ( main.Queue = '3' OR main.Queue = '7' OR main.Queue = '4' OR main.Queue > = '131' OR main.Queue = '169' OR main.Queue = '170' OR main.Queue = '167' OR > main.Queue = '105' OR main.Queue = '42' OR main.Queue = '6' OR main.Queue = > '41' OR main.Queue = '18' OR main.Queue = '1' OR main.Queue = '8' OR > main.Queue = '89' OR main.Queue = '90' OR main.Queue = '43' OR main.Queue = > '68' OR main.Queue = '72' OR main.Queue = '73' OR main.Queue = '17' OR > main.Queue = '158' OR main.Queue = '71' OR main.Queue = '92' OR main.Queue = > '107' OR main.Queue = '95' OR main.Queue = '23' OR main.Queue = '24' OR > main.Queue = '27' OR main.Queue = '86' OR main.Queue = '16' OR main.Queue = > '97' OR main.Queue = '53' OR main.Queue = '66' OR main.Queue = '12' OR > main.Queue = '82' OR main.Queue = '75' OR main.Queue = '51' OR main.Queue = > '69' OR main.Queue = '26' OR main.Queue = '96' OR main.Queue = '152' OR > main.Queue = '93' OR main.Queue = '25' OR main.Queue = '165' OR main.Queue = > '81' OR main.Queue = '29' OR main.Queue = '14' OR main.Queue = '19' OR > main.Queue = '162' OR main.Queue = '140' OR main.Queue = '98' OR main.Queue = > '10' OR main.Queue = '5' OR main.Queue = '133' OR main.Queue = '121' OR > main.Queue = '153' OR main.Queue = '20' OR main.Queue = '154' OR main.Queue = > '126' OR main.Queue = '125' OR main.Queue = '168' OR main.Queue = '134' OR > main.Queue = '137' OR main.Queue = '173' OR main.Queue = '94' OR main.Queue = > '155' OR main.Queue = '138' OR main.Queue = '127' OR main.Queue = '157' OR > main.Queue = '156' OR main.Queue = '132' OR main.Queue = '123' OR main.Queue > = '112' OR main.Queue = '118' OR main.Queue = '117' OR main.Queue = '119' OR > main.Queue = '115' OR main.Queue = '9' OR main.Queue = '122' OR main.Queue = > '160' OR main.Queue = '28' OR main.Queue = '100' OR main.Queue = '44' OR > main.Queue = '45' OR main.Queue = '149' OR main.Queue = '48' OR main.Queue = > '164' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) GROUP > BY main.Status, main.Queue; > > > > > > > > > > QUERY PLAN > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > GroupAggregate (cost=2278.48..2278.69 rows=1 width=20) (actual > time=34.864..35.871 rows=142 loops=1) > -> Sort (cost=2278.48..2278.52 rows=16 width=20) (actual > time=34.852..35.013 rows=2472 loops=1) > Sort Key: status, queue > Sort Method: quicksort Memory: 212kB > -> Bitmap Heap Scan on tickets main (cost=49.55..2278.16 rows=16 > width=20) (actual time=6.384..29.371 rows=2472 loops=1) > Recheck Cond: (((status)::text = 'new'::text) OR > ((status)::text = 'open'::text) OR ((status)::text = 'stalled'::text)) > Filter: (((type)::text = 'ticket'::text) AND (effectiveid = > id) AND ((queue = 3) OR (queue = 7) OR (queue = 4) OR (queue = 131) OR (queue > = 169) OR (queue = 170) OR (queue = 167) OR (queue = 105) OR (queue = 42) OR > (queue = 6) OR (queue = 41) OR (queue = 18) OR (queue = 1) OR (queue = 8) OR > (queue = 89) OR (queue = 90) OR (queue = 43) OR (queue = 68) OR (queue = 72) > OR (queue = 73) OR (queue = 17) OR (queue = 158) OR (queue = 71) OR (queue = > 92) OR (queue = 107) OR (queue = 95) OR (queue = 23) OR (queue = 24) OR > (queue = 27) OR (queue = 86) OR (queue = 16) OR (queue = 97) OR (queue = 53) > OR (queue = 66) OR (queue = 12) OR (queue = 82) OR (queue = 75) OR (queue = > 51) OR (queue = 69) OR (queue = 26) OR (queue = 96) OR (queue = 152) OR > (queue = 93) OR (queue = 25) OR (queue = 165) OR (queue = 81) OR (queue = 29) > OR (queue = 14) OR (queue = 19) OR (queue = 162) OR (queue = 140) OR (queue = > 98) OR (queue = 10) OR (queue = 5) OR (queue = 133) OR (queue = 121) OR > (queue = 153) OR (queue = 20) OR (queue = 154) OR (queue = 126) OR (queue = > 125) OR (queue = 168) OR (queue = 134) OR (queue = 137) OR (queue = 173) OR > (queue = 94) OR (queue = 155) OR (queue = 138) OR (queue = 127) OR (queue = > 157) OR (queue = 156) OR (queue = 132) OR (queue = 123) OR (queue = 112) OR > (queue = 118) OR (queue = 117) OR (queue = 119) OR (queue = 115) OR (queue = > 9) OR (queue = 122) OR (queue = 160) OR (queue = 28) OR (queue = 100) OR > (queue = 44) OR (queue = 45) OR (queue = 149) OR (queue = 48) OR (queue = > 164))) > Rows Removed by Filter: 4097 > -> BitmapOr (cost=49.55..49.55 rows=6153 width=0) (actual > time=3.646..3.646 rows=0 loops=1) > -> Bitmap Index Scan on tickets4 (cost=0.00..19.32 > rows=2408 width=0) (actual time=2.380..2.380 rows=16885 loops=1) > Index Cond: ((status)::text = 'new'::text) > -> Bitmap Index Scan on tickets4 (cost=0.00..29.57 > rows=3707 width=0) (actual time=1.240..1.240 rows=9053 loops=1) > Index Cond: ((status)::text = 'open'::text) > -> Bitmap Index Scan on tickets4 (cost=0.00..0.65 > rows=38 width=0) (actual time=0.024..0.024 rows=71 loops=1) > Index Cond: ((status)::text = 'stalled'::text) > Total runtime: 36.025 ms > (16 rows) > -- Best regards, Ruslan.
