From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Adam Rich <adam.r@sbcglobal.net>
Cc: Bricklen Anderson <banderson@presinet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Thu, 18 Jan 2007 11:24:10 +0100
Message-ID: <45AF4ACA.5060805@tweakers.net> (raw)
In-Reply-To: <090501c73a90$7f154330$6400a8c0@dualcore>
References: <090501c73a90$7f154330$6400a8c0@dualcore>
On 18-1-2007 0:37 Adam Rich wrote:
> 4) Complex queries that might take advantage of the MySQL "Query Cache"
> since the base data never changes
Have you ever compared MySQL's performance with complex queries to
PostgreSQL's? I once had a query which would operate on a recordlist and
see whether there were any gaps larger than 1 between consecutive
primary keys.
Normally that information isn't very usefull, but this time it was.
Since the data was in MySQL I tried several variations of queries in
MySQL... After ten minutes or so I gave up waiting, but left my last
version running. In the mean time I dumped the data, reloaded the data
in PostgreSQL and ran some testqueries there. I came up with a query
that took only 0.5 second on Postgres pretty soon. The query on MySQL
still wasn't finished...
In my experience it is (even with the 5.0 release) easier to get good
performance from complex queries in postgresql. And postgresql gives you
more usefull information on why a query takes a long time when using
explain (analyze). There are some draw backs too of course, but while we
in our company use mysql I switched to postgresql for some readonly
complex query stuff just for its performance...
Besides that, mysql rewrites the entire table for most table-altering
statements you do (including indexes). For small tables that's no issue,
but if you somehow can't add all your indexes in a single statement to a
table you'll be waiting a long time more for new indexes than with
postgresql. And that situation isn't so unusual if you think of a query
which needs an index that isn't there yet. Apart from the fact that it
doesn't have functional indexes and such.
Long story short: MySQL still isn't the best performer when looking at
the more complex queries. I've seen performance which made me assume it
can't optimise sequential scans (when it is forced to loop using a seq
scan it appears to do a new seq scan for each round in the loop...) and
various other cases PostgreSQL can execute much more efficiently.
So unless you run the same queries a lot of times and know of a way to
get it fast enough the initial time, the query cache is not much of a help.
Best regards,
Arjen
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-performance@postgresql.org
Cc: acmmailing@tweakers.net, adam.r@sbcglobal.net, banderson@presinet.com
Subject: Re: Configuration Advice
In-Reply-To: <45AF4ACA.5060805@tweakers.net>
* 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