[request-tracker-maintainers] Bug#512759: request-tracker3.8: Database schema - possible missing index for Postgres on Groups table

[Andrew Robert Nicols]

Package: request-tracker3.8
Version: 3.8.2-1~experimental1
Severity: normal
Tags: patch


This is a similar bug report to #512653. Tested on an instance with 50,000 tickets. Box has 4x3.2Ghz Xeon cores and a
reasonable amount of memory.

Although there are two existing indexes for the Groups table, I've been consistantly seeing one particular query slowing
RT down under Postgres. The query is:

  SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER($1) AND LOWER(Type) = LOWER($2);

And I've tested with the following substitutions for the bind:

  SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged');

Under load, I've seen execution times of > 400ms on this query, though on an unloaded box it's much lower:

rt=# explain analyze SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged');
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on groups  (cost=0.00..5628.92 rows=5 width=66) (actual time=0.023..109.991 rows=1 loops=1)
   Filter: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text))
 Total runtime: 110.039 ms
(3 rows)


Creating a specific index for this query speeds things up significantly (about 1000x faster in terms of actual execution
speed):

rt=# CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type));
CREATE INDEX
rt=# explain analyze SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged');
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on groups  (cost=4.35..23.61 rows=5 width=66) (actual time=0.128..0.130 rows=1 loops=1)
   Recheck Cond: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text))
   ->  Bitmap Index Scan on groups3  (cost=0.00..4.34 rows=5 width=0) (actual time=0.122..0.122 rows=1 loops=1)
         Index Cond: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text))
 Total runtime: 0.172 ms
(5 rows)



I've seen this query called quite often - in fact pretty much any time a permissions check is invoked.


Rather than adding this INDEX, is it necessary to make these case insensitive?

The LOWERs are put in by _MakeClauseCaseInsensitive of DBIx::SearchBuilder::Handle::Pg, which is called on line 362 of
RT::Record by LoadByCols.

The particular queries which this INDEX deal with are called by:
  LoadSystemInternalGroup of Group_Overlay.pm
  LoadSystemRoleGroup of Group_Overlay.pm

Given that there are only 9 distinct types in the groups table, would it be better to remove the case change on at least
the types column? That said, I can't imagine it being that easy to do so for one column...


Anyway, digression aside, patch attached.

Andrew Nicols

-- 
Systems Developer

e: andrew.nicols at luns.net.uk
im: a.nicols at jabber.lancs.ac.uk
t: +44 (0)1524 5 10147

Lancaster University Network Services is a limited company registered in
England and Wales. Registered number: 4311892. Registered office:
University House, Lancaster University, Lancaster, LA1 4YW 

-- System Information:
Debian Release: 5.0
  APT prefers testing
  APT policy: (500, 'testing')
Architecture: i386 (i686)

Kernel: Linux 2.6.26-1-vserver-686-bigmem (SMP w/2 CPU cores)
Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash
-------------- next part --------------
--- schema.Pg	2009-01-22 15:05:25.000000000 +0000
+++ schema.Pg.new	2009-01-23 12:32:30.000000000 +0000
@@ -140,6 +140,7 @@
 );
 CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
 CREATE INDEX Groups2 On Groups  (Type, Instance, Domain);
+CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type));
 
 
 -- }}}