Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. - Mailing list pgsql-performance

explain analyze of original Query:

rt3=# explain analyze SELECT DISTINCT main.* FROM Tickets main  JOIN Groups as Groups_1  ON ( main.id = Groups_1.Instance)   JOIN Principals as Principals_2  ON ( Groups_1.id = Principals_2.ObjectId)   JOIN CachedGroupMembers as CachedGroupMembers_3  ON ( Principals_2.id = CachedGroupMembers_3.GroupId)   JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)    WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (lower(Users_4.EmailAddress) = 'mallah_rajesh@yahoo.com')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority DESC LIMIT 10;                                                                                                                                                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit  (cost=619.93..620.00 rows=1 width=164) (actual time=994.570..994.683 rows=4 loops=1)  ->  Unique  (cost=619.93..620.00 rows=1 width=164) (actual time=994.565..994.672 rows=4 loops=1)        ->  Sort  (cost=619.93..619.93 rows=1 width=164) (actual time=994.561..994.569 rows=8 loops=1)              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..619.92 rows=1 width=164) (actual time=1.374..993.998 rows=8 loops=1)                    ->  Nested Loop  (cost=0.00..610.83 rows=3 width=168) (actual time=0.691..839.633 rows=9617 loops=1)                          ->  Nested Loop  (cost=0.00..476.17 rows=1 width=168) (actual time=0.524..616.937 rows=3209 loops=1)                                ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168) (actual time=0.376..503.774 rows=3209 loops=1)                                      ->  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=164) (actual time=0.114..60.044 rows=3209 loops=1)                                            Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))                                      ->  Index Scan using groups1 on groups groups_1  (cost=0.00..5.90 rows=1 width=12) (actual time=0.111..0.119 rows=1 loops=3209)                                            Index Cond: (((groups_1."domain")::text = 'RT::Ticket-Role'::text) AND (("outer".id)::text = (groups_1.instance)::text) AND ((groups_1."type")::text = 'Requestor'::text))                                ->  Index Scan using principals2 on principals principals_2  (cost=0.00..4.62 rows=1 width=8) (actual time=0.015..0.018 rows=1 loops=3209)                                      Index Cond: ("outer".id = principals_2.objectid)                                      Filter: ((principaltype)::text = 'Group'::text)                          ->  Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..134.06 rows=47 width=8) (actual time=0.015..0.026 rows=3 loops=3209)                                Index Cond: ("outer".id = cachedgroupmembers_3.groupid)                    ->  Index Scan using users_pkey on users users_4  (cost=0.00..3.02 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=9617)                          Index Cond: ("outer".memberid = users_4.id)                          Filter: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text)Total runtime: 995.326 ms
(21 rows)
rt3=#

999 ms is not that bad but u think it deserves this many ms?


Nopes the query are not Equiv , earlier one returns 4 rows and the below one none,
can you spot any obvious and resend plz. thats why i did not do an explain analyze

rt3=# SELECT *
rt3-#   FROM tickets
rt3-#  WHERE id IN (
rt3(#        SELECT groups.instance
rt3(#          FROM groups
rt3(#          JOIN principals ON (groups.id = principals.objectid)
rt3(#          JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid)
rt3(#          JOIN users ON (cachedgroupmembers.memberid = users.id)
rt3(#         WHERE users.emailaddress = 'mallah_rajesh@yahoo.com'
rt3(#           AND groups.domain = 'RT::Ticket-Role'
rt3(#           AND groups.type   = 'Requestor'
rt3(#           AND principals.principaltype = 'group'
rt3(#   )
rt3-#   AND type = 'ticket'
rt3-#   AND effectiveid = tickets.id
rt3-#   AND (status = 'new' OR status = 'open')
rt3-# ORDER BY priority DESC
rt3-# LIMIT 10;
id | effectiveid | queue | type | issuestatement | resolution | owner | subject | initialpriority | finalpriority | priority | timeestimated | timeworked | status | timeleft | told | starts | started | due | resolved | lastupdatedby | lastupdated | creator | created | disabled
----+-------------+-------+------+----------------+------------+-------+---------+-----------------+---------------+----------+---------------+------------+--------+----------+------+--------+---------+-----+----------+---------------+-------------+---------+---------+----------
(0 rows)

Time: 2670.85 ms
rt3=#

Well it may be of interest to write the query in best possible way
but i am not sure if it really helps the RT application becoz i do
not know whether DBIx::SearchBuilder would currently allow
auto generation of such arbitrary SQLs.

Regds
Mallah.




Greg Stark wrote:

Rajesh Kumar Mallah <mallah@trade-india.com> writes:
 

rt3=# explain 

SELECT DISTINCT main.*  FROM (((         (Tickets main  JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance))         JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)        ) JOIN CachedGroupMembers as CachedGroupMembers_3  ON ( Principals_2.id = CachedGroupMembers_3.GroupId)       ) JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)      )WHERE ((main.EffectiveId = main.id))  AND ((main.Type = 'ticket'))  AND (((    (Users_4.EmailAddress = 'mallah_rajesh@yahoo.com')         AND (Groups_1.Domain = 'RT::Ticket-Role')         AND (Groups_1.Type = 'Requestor')         AND (Principals_2.PrincipalType = 'Group')        ))        AND ((main.Status = 'new') OR (main.Status = 'open'))      )ORDER BY main.Priority DESC LIMIT 10;   

So this query seems to be going the long way around to do the equivalent of an
IN clause. Presumably because as far as I know mysql didn't support IN
subqueries until recently.

Can you do an "explain analyze" on the above query and the following rewritten
one in 7.4? The "analyze" is important because it'll give real timing
information. And it's important that it be on 7.4 as there were improvements
in this area specifically in 7.4.

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 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;