Re: Postgres performance - Mailing list pgsql-sql

On Wed, 2005-03-02 at 15:45, PFC wrote:
> > The reason PostgreSQL is slower is because it (and by extension the team
> > behind it) cares about your data.
> 
>     Sure, postgres is (a bit but not much) slower for a simple query like  
> SELECT * FROM one table WHERE id=some number, and postgres is a lot slower  
> for UPDATES (although I heard that it's faster than MySQL InnoDB)... but  
> try a query with a join on few tables, even a simple one, and postgres  
> will outperform mysql, sometimes by 2x, sometimes 1000 times. I had a case  
> with a join between 4 tables, two of them having 50k records ; I was only  
> pulling 6 records... mysql spent half a second and postgres 0.5 ms... hell  
> !

Or better yet, a query like this:

select a.lt ,b.perspective as YYY_pers,b.averageresponsetime as YYY_aver,b.lowestresponsetime as
YYY_lowe,b.highestresponsetimeas YYY_high,b.totalcount as YYY_tota,c.perspective as XXX_pers,c.averageresponsetime as
XXX_aver,c.lowestresponsetimeas XXX_lowe,c.highestresponsetime as XXX_high,c.totalcount as XXX_tota,d.perspective as
BBB_pers,d.averageresponsetimeas BBB_aver,d.lowestresponsetime as BBB_lowe,d.highestresponsetime as
BBB_high,d.totalcountas BBB_tota,e.perspective as AAA_pers,e.averageresponsetime as AAA_aver,e.lowestresponsetime as
AAA_lowe,e.highestresponsetimeas AAA_high,e.totalcount as AAA_tota,f.perspective as CCC_pers,f.averageresponsetime as
CCC_aver,f.lowestresponsetimeas CCC_lowe,f.highestresponsetime as CCC_high,f.totalcount as CCC_tota,g.perspective as
ZZZ_pers,g.averageresponsetimeas ZZZ_aver,g.lowestresponsetime as ZZZ_lowe,g.highestresponsetime as
ZZZ_high,g.totalcountas ZZZ_tota 
 
from (   select distinct date_trunc('minutes', lastflushtime) as lt from businessrequestsummary   where lastflushtime
between'2005-01-01 00:00:00' and '2005-03-31 00:00:00'
 
) as a 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='YYY'
 
)as b on (a.lt=b.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='XXX'
 
)as c on (a.lt=c.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='BBB'
 
)as d on (a.lt=d.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='AAA'
 
)as e on (a.lt=e.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='CCC'
 
)as f on (a.lt=f.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as
lt,perspective,averageresponsetime,lowestresponsetime,highestresponsetime,totalcountfrombusinessrequestsummary where
perspective='ZZZ'
 
)as g on (a.lt=g.lt) 

Basically, the more complex the query gets, the worse MySQL generally does, since it's query planner
is a pretty simple rules based one.