Date: Sat, 5 Jul 2003 16:40:52 -0700
Subject: Re: PostgreSQL vs. MySQL
Content-Type: text/plain; charset=US-ASCII; format=flowed
Mime-Version: 1.0 (Apple Message framework v552)
Cc: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>,
	<pgsql-performance@postgresql.org>
To: "Brian Tarbox" <btarbox@theworld.com>
From: Hilmar Lapp <hlapp@gmx.net>
In-Reply-To: <01bf01c34235$a4c8aa60$01000001@trouble>
Message-Id: <1D2B4368-AF42-11D7-9143-000393B4BFF6@gmx.net>
Content-Transfer-Encoding: 7bit


On Friday, July 4, 2003, at 07:07  AM, Brian Tarbox wrote:

> We had about 40 tables in the db, with joined queries on about 8-12 
> tables.
>

A while ago a tested a moderately complex schema on MySQL, Pg, and 
Oracle. I usually heavily normalize schemas and then define views as a 
denormalized API, which sends MySQL to the book of toys already. The 
views more often than not would join anywhere from 6-12 tables, using 
plain (as opposed to compound) foreign keys to primary key straight 
joins.

I noticed that Pg was more than an order of magnitude slower for joins 
 > 8 tables than Oracle. I won't claim that none of this can have been 
due to lack of tuning. My point is the following though. After I dug in 
it turned out that of the 4 secs Pg needed to execute the query it 
spent 3.9 secs in the planner. The execution plan Pg came up with was 
pretty good - it just needed an extraordinary amount of time to arrive 
at it, spoiling its own results.

Asking this list I then learned how to tweak GEQO such that it would 
pick up the planning and do it faster than it would otherwise. I was 
able to get the planner time down to a quarter - still a multitude of 
the actual execution time.

I was told on this list that query planning suffers from combinatorial 
explosion very quickly - and I completely buy that. It's just - Oracle 
planned the same query in a fraction of a second, using the cost-based 
optimizer, on a slower machine. I've seen it plan 15-table joins in 
much less than a second, and I have no idea how it would do that. In 
addition, once you've prepared a query in Oracle, the execution plan is 
pre-compiled.

If I were a CS student I'd offer myself to the hall of humiliation and 
set out to write a fast query planner for Pg ...

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------