pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: mallah@trade-india.com
Cc: pgsql-performance@postgresql.org
Cc: jesse@fsck.com
Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Date: Wed, 29 Oct 2003 18:23:07 -0500
Message-ID: <23780.1067469787@sss.pgh.pa.us> (raw)
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>

<mallah@trade-india.com> 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



view thread (12+ messages)  latest in thread

Message-ID: <23780.1067469787@sss.pgh.pa.us>
Permalink:  ../23780.1067469787@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23780.1067469787@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, mallah@trade-india.com, jesse@fsck.com
  Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
  In-Reply-To: <23780.1067469787@sss.pgh.pa.us>

* 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