pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: jesse@fsck.com
Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Date: Thu, 30 Oct 2003 16:34:38 +0530
Message-ID: <200310301634.38136.mallah@trade-india.com> (raw)
In-Reply-To: <23780.1067469787@sss.pgh.pa.us>
References: <33280.203.145.130.142.1067455363.squirrel@mail.trade-india.com>
	<33288.203.145.130.142.1067456744.squirrel@mail.trade-india.com>
	<23780.1067469787@sss.pgh.pa.us>

On Thursday 30 Oct 2003 4:53 am, you wrote:
> <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


Yes that was an optimisation on haste the simplification was not 
accurate. I will work on it again. But incidently both the SQLs
produced the same results which *may* mean that the query could
have been done in a simpler manner.


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


Yes the SQL generator is not doing the best things at the moment
and the author(Jesse) is aware of it and looking forward to our
help in optimising it.


>
> (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.)

I do not think the database schema is a mess.
The ACL system in RT and RT itself is quite comprehensive.
The problem is with the Query Generator.

Apologies for delayed response to your email.

Regards
Mallah.


>
> 			regards, tom lane




view thread (12+ messages)

Message-ID: <200310301634.38136.mallah@trade-india.com>
Permalink:  ../200310301634.38136.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200310301634.38136.mallah@trade-india.com

 · 

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