[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