> 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?
Yes both are primary keys. and i did vacuum full verbose analyze;
Have you tried
> upping the statistics target on those two fields?
> I assume they are compatible types.
Yes they are
> You might try 'set enable_mergejoin = false' and see if it does something faster here. Just a
Did not help
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
In response to
pgsql-performance by date
|Next:||From: markw||Date: 2003-10-29 22:26:11|
|Subject: Re: analyzing postgresql performance for dbt-2|
|Previous:||From: scott.marlowe||Date: 2003-10-29 21:51:18|
|Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with|