From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: Sat, 01 Nov 2003 00:44:07 -0500
Message-ID: <3FA34827.1030705@trade-india.com> (raw)
In-Reply-To: <87r80ud0nw.fsf@stark.dyndns.tv>
References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com>
<20031029202920.GZ7337@pallas.fsck.com>
<33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com>
<200310301702.00275.mallah@trade-india.com>
<m33cdakhv7.fsf@wolfe.cbbrowne.com>
<13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com>
<m3smlaiww8.fsf@wolfe.cbbrowne.com>
<3FA323C2.2060302@trade-india.com>
<87r80ud0nw.fsf@stark.dyndns.tv>
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 tickets
> WHERE 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;
>
>
>
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mallah@trade-india.com, gsstark@mit.edu, cbbrowne@acm.org, jesse@bestpractical.com
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
In-Reply-To: <3FA34827.1030705@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox