From: Jonah H. Harris <jonah.harris@gmail.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@lusis.org, "PGSQL Performance" <pgsql-performance@postgresql.org>
Subject: Re: Optimizer internals
Date: Fri, 16 Jun 2006 09:43:55 -0400
Message-ID: <36e682920606160643u1d2f559fxe09f8acc970c1c53@mail.gmail.com> (raw)
In-Reply-To: <8764j18cea.fsf@stark.xeocode.com>
References: <c841561b0606151105v459d830era158b9eea5f59a7e@mail.gmail.com>
<1150396425.31200.66.camel@archimedes>
<87ver18hu9.fsf@stark.xeocode.com>
<36e682920606160551v6b98bcfek32f01b895c53e057@mail.gmail.com>
<8764j18cea.fsf@stark.xeocode.com>
On 16 Jun 2006 09:21:01 -0400, Greg Stark <gsstark@mit.edu> wrote:
> Well Oracle has to do almost all that same work, it's just doing it in a
> separate place called a rollback segment.
Well, it's not really the same work. The process by which Oracle
manages UNDO is actually pretty simple and efficient, but complex in
its implementation. There has also been some significant performance
improvements in this area in both 9i and 10g.
> There are pros and cons especially where it comes
> to indexes, but also where it comes to what happens
> when the new record is larger than the old one.
Certainly, you want to avoid row chaining at all costs; which is why
PCTFREE is there. I have researched update-in-place for PostgreSQL
and can avoid row-chaining... so I think we can get the same benefit
without the management and administration cost.
> IMHO the biggest problem Postgres has is when you're
> updating a lot of records in a table with little free space.
Yes, this is certainly the most noticible case. This is one reason
I'm behind the freespace patch. Unfortunately, a lot of inexperienced
people use VACUUM FULL and don't understand why VACUUM is *generally*
better.(to free up block-level freespace and update FSM) assuming they
have enough hard disk space for the database.
> That and of course the visibility bitmap that has been
> much-discussed
I'd certainly like to see it.
> I wouldnt' say the benchmarks are flawed but I also
> don't think you can point to any specific design
> feature and say it's essential just on the basis of
> bottom-line results. You have to look at the actual
> benefit the specific wins.
True.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
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: jonah.harris@gmail.com, gsstark@mit.edu, mark.lewis@mir3.com
Subject: Re: Optimizer internals
In-Reply-To: <36e682920606160643u1d2f559fxe09f8acc970c1c53@mail.gmail.com>
* 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