Subject: Re: Postgres performance
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: PFC <lists@boutiquenumerique.com>
Cc: mauro <bertolima@yahoo.it>, pgsql-sql@postgresql.org
In-Reply-To: <opsm05ecu9th1vuj@musicbox>
References: <d64dda1e.0502230229.3deaf864@posting.google.com>
	<421D8ABC.2030601@archonet.com>
	<d64dda1e.0503010252.67c2312d@posting.google.com>
	<1109775613.30529.359.camel@state.g2switchworks.com>
	<opsm05ecu9th1vuj@musicbox>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1109801175.30529.379.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 02 Mar 2005 16:06:16 -0600

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.highestresponsetime as YYY_high,
	b.totalcount as YYY_tota,
	c.perspective as XXX_pers,
	c.averageresponsetime as XXX_aver,
	c.lowestresponsetime as XXX_lowe,
	c.highestresponsetime as XXX_high,
	c.totalcount as XXX_tota,
	d.perspective as BBB_pers,
	d.averageresponsetime as BBB_aver,
	d.lowestresponsetime as BBB_lowe,
	d.highestresponsetime as BBB_high,
	d.totalcount as BBB_tota,
	e.perspective as AAA_pers,
	e.averageresponsetime as AAA_aver,
	e.lowestresponsetime as AAA_lowe,
	e.highestresponsetime as AAA_high,
	e.totalcount as AAA_tota,
	f.perspective as CCC_pers,
	f.averageresponsetime as CCC_aver,
	f.lowestresponsetime as CCC_lowe,
	f.highestresponsetime as CCC_high,
	f.totalcount as CCC_tota,
	g.perspective as ZZZ_pers,
	g.averageresponsetime as ZZZ_aver,
	g.lowestresponsetime as ZZZ_lowe,
	g.highestresponsetime as ZZZ_high,
	g.totalcount as 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,
	totalcount
	from businessrequestsummary where perspective ='YYY'
)as b on (a.lt=b.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as lt,
	perspective,
	averageresponsetime,
	lowestresponsetime,
	highestresponsetime,
	totalcount
	from businessrequestsummary where perspective ='XXX'
)as c on (a.lt=c.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as lt,
	perspective,
	averageresponsetime,
	lowestresponsetime,
	highestresponsetime,
	totalcount
	from businessrequestsummary where perspective ='BBB'
)as d on (a.lt=d.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as lt,
	perspective,
	averageresponsetime,
	lowestresponsetime,
	highestresponsetime,
	totalcount
	from businessrequestsummary where perspective ='AAA'
)as e on (a.lt=e.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as lt,
	perspective,
	averageresponsetime,
	lowestresponsetime,
	highestresponsetime,
	totalcount
	from businessrequestsummary where perspective ='CCC'
)as f on (a.lt=f.lt) 
left join
(   select date_trunc('minutes', lastflushtime) as lt,
	perspective,
	averageresponsetime,
	lowestresponsetime,
	highestresponsetime,
	totalcount
	from businessrequestsummary 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.