X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BE5A0D1B8D7 for ; Thu, 30 Oct 2003 16:50:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88821-08 for ; Thu, 30 Oct 2003 12:49:56 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 1AD9BD1B923 for ; Thu, 30 Oct 2003 12:49:54 -0400 (AST) Received: (qmail 24761 invoked from network); 30 Oct 2003 16:51:11 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 30 Oct 2003 16:51:11 -0000 Message-ID: <3FA3265B.6040707@trade-india.com> Date: Fri, 31 Oct 2003 22:19:55 -0500 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. 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> <28551.1067530272@sss.pgh.pa.us> In-Reply-To: <28551.1067530272@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------010705060908090409080604" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/763 X-Sequence-Number: 4511 This is a multi-part message in MIME format. --------------010705060908090409080604 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Rajesh Kumar Mallah writes: > > >>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 >> ... >> >> > >I think the reason for the performance difference is that 7.3 treats >JOIN syntax as forcing a particular join order, while 7.4 doesn't. > Just out of curiosity , how does 7.4 determine the optimal Join Order? is it GEQO in case of 7.4 although i did not enable it explicitly? Thanks for the reply , I sent the EXPLAINs also just now. What i really want is to help improving the Pg specific Component for DBIx::SearchBuilder. The module is being widely used in the mod_perl world and has impact on the performance perception of PostgreSQL. > > regards, tom lane > > --------------010705060908090409080604 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
  
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
 ...
    

I think the reason for the performance difference is that 7.3 treats
JOIN syntax as forcing a particular join order, while 7.4 doesn't.

Just out of curiosity , how does 7.4 determine the optimal Join Order?
is it GEQO in case of 7.4 although i did not enable it explicitly?
Thanks for the reply , I sent the EXPLAINs also just now.

What i really want is to help improving the Pg specific Component
for DBIx::SearchBuilder. The module is being widely used in
the  mod_perl world and has impact on the performance perception
of PostgreSQL.

			regards, tom lane
  

--------------010705060908090409080604--