From: Scott Marlowe <smarlowe@g2switchworks.com>
To: PFC <lists@boutiquenumerique.com>
Cc: mauro <bertolima@yahoo.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres performance
Date: Wed, 02 Mar 2005 16:06:16 -0600
Message-ID: <1109801175.30529.379.camel@state.g2switchworks.com> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, lists@boutiquenumerique.com, bertolima@yahoo.it
Subject: Re: Postgres performance
In-Reply-To: <1109801175.30529.379.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox