From: | Hilmar Lapp <hlapp(at)gmx(dot)net> |
---|---|
To: | "Brian Tarbox" <btarbox(at)theworld(dot)com> |
Cc: | "Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL vs. MySQL |
Date: | 2003-07-05 23:40:52 |
Message-ID: | 1D2B4368-AF42-11D7-9143-000393B4BFF6@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs pgsql-general pgsql-hackers pgsql-performance |
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
-------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2003-07-06 00:11:58 | Re: PostgreSQL vs. MySQL |
Previous Message | Grega Bremec | 2003-07-05 16:39:37 | Re: PostgreSQL vs. MySQL |
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2003-07-06 00:11:58 | Re: PostgreSQL vs. MySQL |
Previous Message | Alvaro Herrera | 2003-07-05 20:53:34 | Re: problem with regexp (cant quote +) |
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2003-07-06 00:11:58 | Re: PostgreSQL vs. MySQL |
Previous Message | Tatsuo Ishii | 2003-07-05 23:38:18 | Re: vacuum does not reclaim rows |
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2003-07-06 00:11:58 | Re: PostgreSQL vs. MySQL |
Previous Message | Josh Berkus | 2003-07-05 22:58:25 | Re: Moving postgresql.conf tunables into 2003... |