[rt-users] postgres slowdowns? (pg 7.3.4, rt 3.0.4)

Justin Hawkins justin at internode.com.au
Thu Oct 16 20:56:07 EDT 2003


On Thu, Oct 16, 2003 at 10:04:34AM -0400, Vivek Khera wrote:
> >>>>> "JH" == Justin Hawkins <justin at internode.com.au> writes:
> 
> >> Or create an index on lower(EmailAddress) on that table:
> >> 
> >> CREATE INDEX users5 ON users (lower(emailaddress));
> >> 
> 
> JH> Tried, but no dice. Are you interested in seeing the postgres
> JH> EXPLAIN output's before and after the index?
> 
> yes, that would be helpful.  also, run 'vacuum analyze users' so that
> the index has proper statistics on it.

OK, here it is without the index, just having done a vacuum analyse:

                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1358.63..1358.70 rows=1 width=212)
   ->  Unique  (cost=1358.63..1358.70 rows=1 width=212)
         ->  Sort  (cost=1358.63..1358.63 rows=1 width=212)
               Sort Key: main.priority, main.id, main.effectiveid, main.queue, main."type", main.issuestatement, main.resolution, main."owner", main.subject, main.initialpriority, main.finalpriority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled
               ->  Nested Loop  (cost=0.00..1358.62 rows=1 width=212)
                     ->  Nested Loop  (cost=0.00..1353.73 rows=1 width=208)
                           ->  Nested Loop  (cost=0.00..1342.46 rows=2 width=200)
                                 ->  Nested Loop  (cost=0.00..1300.48 rows=1 width=192)
                                       ->  Nested Loop  (cost=0.00..1295.52 rows=1 width=184)
                                             Join Filter: (("outer".id)::text = ("inner".instance)::text)
                                             ->  Seq Scan on tickets main  (cost=0.00..333.46 rows=1 width=173)
                                                   Filter: ((effectiveid = id) AND ("type" = 'ticket'::character varying) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)))
                                             ->  Seq Scan on groups groups_1  (cost=0.00..869.25 rows=6187 width=11)
                                                   Filter: (("domain" = 'RT::Ticket-Role'::character varying) AND ("type" = 'Requestor'::character varying))
                                       ->  Index Scan using principals2 on principals principals_2  (cost=0.00..4.95 rows=1 width=8)
                                             Index Cond: ("outer".id = principals_2.objectid)
                                             Filter: (principaltype = 'Group'::character varying)
                                 ->  Index Scan using group1 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..41.83 rows=12 width=8)
                                       Index Cond: ("outer".id = cachedgroupmembers_3.groupid)
                           ->  Index Scan using principals_pkey on principals principals_4  (cost=0.00..4.95 rows=1 width=8)
                                 Index Cond: ("outer".memberid = principals_4.id)
                     ->  Index Scan using users_pkey on users users_5  (cost=0.00..3.34 rows=1 width=4)
                           Index Cond: ("outer".objectid = users_5.id)
                           Filter: (lower((emailaddress)::text) = 'root at rt3.internode.com.au'::text)
(24 rows)


Create the index, vacuum analyse again, and re-run the explain:

                                                                                                                                                                                                      QUERY PLAN                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1358.60..1358.66 rows=1 width=212)
   ->  Unique  (cost=1358.60..1358.66 rows=1 width=212)
         ->  Sort  (cost=1358.60..1358.60 rows=1 width=212)
               Sort Key: main.priority, main.id, main.effectiveid, main.queue, main."type", main.issuestatement, main.resolution, main."owner", main.subject, main.initialpriority, main.finalpriority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled
               ->  Nested Loop  (cost=0.00..1358.59 rows=1 width=212)
                     ->  Nested Loop  (cost=0.00..1353.73 rows=1 width=208)
                           ->  Nested Loop  (cost=0.00..1342.46 rows=2 width=200)
                                 ->  Nested Loop  (cost=0.00..1300.48 rows=1 width=192)
                                       ->  Nested Loop  (cost=0.00..1295.52 rows=1 width=184)
                                             Join Filter: (("outer".id)::text = ("inner".instance)::text)
                                             ->  Seq Scan on tickets main  (cost=0.00..333.46 rows=1 width=173)
                                                   Filter: ((effectiveid = id) AND ("type" = 'ticket'::character varying) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)))
                                             ->  Seq Scan on groups groups_1  (cost=0.00..869.25 rows=6187 width=11)
                                                   Filter: (("domain" = 'RT::Ticket-Role'::character varying) AND ("type" = 'Requestor'::character varying))
                                       ->  Index Scan using principals2 on principals principals_2  (cost=0.00..4.95 rows=1 width=8)
                                             Index Cond: ("outer".id = principals_2.objectid)
                                             Filter: (principaltype = 'Group'::character varying)
                                 ->  Index Scan using group1 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..41.83 rows=12 width=8)
                                       Index Cond: ("outer".id = cachedgroupmembers_3.groupid)
                           ->  Index Scan using principals_pkey on principals principals_4  (cost=0.00..4.95 rows=1 width=8)
                                 Index Cond: ("outer".memberid = principals_4.id)
                     ->  Index Scan using users_pkey on users users_5  (cost=0.00..3.32 rows=1 width=4)
                           Index Cond: ("outer".objectid = users_5.id)
                           Filter: (lower((emailaddress)::text) = 'root at rt3.internode.com.au'::text)
(24 rows)

[Sorry about the massive lines, but I didn't want to wrap it and break the nesting]

> JH> I tested the ILIKE solution and it does work. It reduces query
> JH> time to less than one second. I expect this may not be suitable in
> JH> terms of keeping this cross-database-platform compatible.
> 
> Well, if you only need to change the schema, then using a non
> SQL-standard query would not be needed.  Or is ILIKE a standard?

ILIKE is a postgres extension. Annoyingly it exists because LIKE is supposed 
to be a case-sensitive match but MySQL treats it as case-insensitive.

	- Justin



More information about the rt-users mailing list