[prev in list] [next in list] [prev in thread] [next in thread] 

List:       rt-users
Subject:    Re: [rt-users] UseSQLForACLChecks performance problem on RT 3.8
From:       Ruslan Zakirov <ruz () bestpractical ! com>
Date:       2013-01-31 23:28:35
Message-ID: CAMOxC8uOneep8Pqic_Y43oDPfadei9YSWtfW6Zffet6dkxwPhg () mail ! gmail ! com
[Download RAW message or body]

On Thu, Jan 31, 2013 at 10:24 PM, ktm@rice.edu <ktm@rice.edu> wrote:
> On Thu, Jan 31, 2013 at 09:02:59PM +0400, Ruslan Zakirov wrote:
> > On Thu, Jan 31, 2013 at 8:45 PM, ktm@rice.edu <ktm@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.

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.


[prev in list] [next in list] [prev in thread] [next in thread]