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 F0FDAD1B4E8 for ; Wed, 29 Oct 2003 23:23:37 +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 26407-03 for ; Wed, 29 Oct 2003 19:23:08 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 02C27D1B56A for ; Wed, 29 Oct 2003 19:23:06 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9TNN7it023781; Wed, 29 Oct 2003 18:23:07 -0500 (EST) To: mallah@trade-india.com Cc: pgsql-performance@postgresql.org, jesse@fsck.com Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) In-reply-to: <33288.203.145.130.142.1067456744.squirrel@mail.trade-india.com> References: <33280.203.145.130.142.1067455363.squirrel@mail.trade-india.com> <33288.203.145.130.142.1067456744.squirrel@mail.trade-india.com> Comments: In-reply-to message dated "Thu, 30 Oct 2003 01:15:44 +0530" Date: Wed, 29 Oct 2003 18:23:07 -0500 Message-ID: <23780.1067469787@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/735 X-Sequence-Number: 4483 writes: > Actually PostgreSQL is at par with MySQL when the query is being > Properly Written(simplified) These are not the same query, though. Your original looks like SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC where the replacement is SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC ; You have made the condition "ACL_2.PrincipalId = Principals_1.id" required for all cases, where before it appeared in only one arm of an OR condition. If the second query is correct, then the first one is wrong, and your real problem is that your SQL generator is broken. (I'd argue that the SQL generator is broken anyway ;-) if it generates such horrible conditions as that. Or maybe the real problem is that the database schema is a mess and needs rethinking.) regards, tom lane