Re: Postgres performance

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.