public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hilmar Lapp <[email protected]>
To: Brian Tarbox <[email protected]>
Cc: Shridhar Daithankar <[email protected]>
Cc: [email protected]
Subject: Re: PostgreSQL vs. MySQL
Date: Sat, 5 Jul 2003 16:40:52 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <01bf01c34235$a4c8aa60$01000001@trouble>
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
-------------------------------------------------------------
view thread (66+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: PostgreSQL vs. MySQL
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox