[rt-users] UseSQLForACLChecks performance problem on RT 3.8
ktm at rice.edu
ktm at rice.edu
Thu Jan 31 13:24:16 EST 2013
On Thu, Jan 31, 2013 at 09:02:59PM +0400, Ruslan Zakirov wrote:
> On Thu, Jan 31, 2013 at 8:45 PM, ktm at rice.edu <ktm at rice.edu> 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.
>
> > 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)
More information about the rt-users
mailing list