UseSQLForACLChecks performance problem on RT 3.8

RT Users
1

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.

Regards,
Ken

QueueSummary (4.35 KB)

2

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.

3

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)

4

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.

5

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.

Yes, it is for the whole page load. I am glad that I was not missing
some low-hanging fruit. Thank you for the help.

Regards,
Ken