Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)

From: <mallah(at)trade-india(dot)com>
To: <scott(dot)marlowe(at)ihs(dot)com>
Cc: <mallah(at)trade-india(dot)com>, <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 RT(DBIx::SearchBuilder)
Date: 2003-10-29 22:17:09
Message-ID: 33509.203.145.130.142.1067465829.squirrel@mail.trade-india.com (view raw or flat)
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?

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


Did not help

regds
mallah.





-----------------------------------------
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.
http://www.trade-india.com/dyn/gdh/eyp/



In response to

pgsql-performance by date

Next:From: markwDate: 2003-10-29 22:26:11
Subject: Re: analyzing postgresql performance for dbt-2
Previous:From: scott.marloweDate: 2003-10-29 21:51:18
Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group