From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | <mallah(at)trade-india(dot)com> |
Cc: | <sean(at)chittenden(dot)org>, <pgsql-performance(at)postgresql(dot)org>, <jesse(at)fsck(dot)com> |
Subject: | Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with |
Date: | 2003-10-29 21:51:18 |
Message-ID: | Pine.LNX.4.33.0310291446430.22074-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, 30 Oct 2003 mallah(at)trade-india(dot)com wrote:
> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But the
> >> question is my does PostgreSQL suffer so badly ?? I think not all developers write very nice
> >> SQLs.
> >>
> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal becoz of
> >> malformed SQLs. [ specially on database of my choice ;-) ]
> >
> > Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to get some
> > useful help from this list. Until then, it's very hard to speculate as to why PostgreSQL is
> > slower. -sc
>
> Here It is:
>
> in case they are illegeble please lemme know i will attach it as .txt
> files.
>
> Slower One:
>
> explain analyze 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 ;
Note here:
Merge Join
(cost=1788.68..4735.71 rows=1 width=85)
(actual time=597.540..1340.526 rows=20153 loops=1)
Merge Cond: ("outer".id = "inner".id)
This estimate is WAY off. Are both of those fields indexed and analyzed?
Have you tried upping the statistics target on those two fields?
I assume they are compatible types.
You might try 'set enable_mergejoin = false' and see if it does something
faster here. Just a guess.
From | Date | Subject | |
---|---|---|---|
Next Message | mallah | 2003-10-29 22:17:09 | Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) |
Previous Message | mallah | 2003-10-29 20:47:09 | Query puts 7.3.4 on endless loop but 7.4beta5 is fine. |