Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. - Mailing list pgsql-performance
| From | Rajesh Kumar Mallah |
|---|---|
| Subject | Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. |
| Date | |
| Msg-id | 3FA373F3.9040103@trade-india.com Whole thread Raw |
| In response to | Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. (Greg Stark <gsstark@mit.edu>) |
| List | pgsql-performance |
But the new version at lease works on 7.3 instead of putting
it in an infinite loop.
rt3=# explain analyze SELECT * from tickets where id in ( SELECT groups.instance FROM groups
rt3(# JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON
rt3(# (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id)
rt3(# WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' AND groups.domain = 'RT::Ticket-Role'
rt3(# AND groups.type = 'Requestor' AND principals.principaltype = 'Group' ) AND type = 'ticket' AND
rt3-# effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.07 rows=4 loops=1)
-> Sort (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.05 rows=4 loops=1)
Sort Key: priority
-> Seq Scan on tickets (cost=0.00..88073404.72 rows=1 width=163) (actual time=2525.48..2858.95 rows=4 loops=1)
Filter: (("type" = 'ticket'::character varying) AND (effectiveid = id) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)) AND (subplan))
SubPlan
-> Materialize (cost=8443.38..8443.38 rows=66 width=32) (actual time=0.79..0.81 rows=14 loops=3209)
-> Hash Join (cost=3698.35..8443.38 rows=66 width=32) (actual time=1720.53..2525.07 rows=14 loops=1)
Hash Cond: ("outer".memberid = "inner".id)
-> Hash Join (cost=3329.03..7973.87 rows=13247 width=28) (actual time=1225.83..2458.48 rows=31123 loops=1)
Hash Cond: ("outer".groupid = "inner".id)
-> Seq Scan on cachedgroupmembers (cost=0.00..3456.51 rows=204551 width=8) (actual time=0.06..638.91 rows=204551 loops=1)
-> Hash (cost=3315.71..3315.71 rows=5325 width=20) (actual time=1225.51..1225.51 rows=0 loops=1)
-> Hash Join (cost=1355.70..3315.71 rows=5325 width=20) (actual time=529.02..1191.94 rows=10431 loops=1)
Hash Cond: ("outer".objectid = "inner".id)
-> Seq Scan on principals (cost=0.00..1583.76 rows=61940 width=8) (actual time=0.02..450.42 rows=62097 loops=1)
Filter: (principaltype = 'Group'::character varying)
-> Hash (cost=1338.03..1338.03 rows=7068 width=12) (actual time=528.58..528.58 rows=0 loops=1)
-> Index Scan using groups_domain on groups (cost=0.00..1338.03 rows=7068 width=12) (actual time=0.18..498.04 rows=10431 loops=1)
Index Cond: ("domain" = 'RT::Ticket-Role'::character varying)
Filter: ("type" = 'Requestor'::character varying)
-> Hash (cost=369.08..369.08 rows=101 width=4) (actual time=0.10..0.10 rows=0 loops=1)
-> Index Scan using users_emailaddress on users (cost=0.00..369.08 rows=101 width=4) (actual time=0.09..0.10 rows=1 loops=1)
Index Cond: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text)
Total runtime: 2859.34 msec
(25 rows)
Greg Stark wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:Nopes the query are not Equiv , earlier one returns 4 rows and the below one none,Sorry, i lowercased a string constant and dropped the lower() on email. Try this: SELECT * FROM ticketsWHERE id IN ( SELECT groups.instance FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10;
pgsql-performance by date: