Re: Optimizer internals

On 16 Jun 2006 09:21:01 -0400, Greg Stark <gsstark(at)mit(dot)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(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/