Message-ID: <45AF4ACA.5060805@tweakers.net>
Date: Thu, 18 Jan 2007 11:24:10 +0100
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: Adam Rich <adam.r@sbcglobal.net>
Cc: 'Bricklen Anderson' <banderson@presinet.com>,
	pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
References: <090501c73a90$7f154330$6400a8c0@dualcore>
In-Reply-To: <090501c73a90$7f154330$6400a8c0@dualcore>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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