Re: Postgres Internals

Dip wrote:
>
> Hi All,
>
> Im running some benchmarking tests on mysql, postgres and interbase
> database servers.
>
> does anyone know the reasons why or know where i can find out some
> technical reasons why the postgres database server is particularly good
> in relation to the others or just by itself e.g. use of query
> optimisation or indexing etc. I'm only looking at creates, selects,
> inserts, update and delete statements. I've noticed it is slow at
> inserting data into tables, but especially quick at doing complex
> selects (i.e. containing many joins). Why is this so?

I suspect because on an UPDATE postgres creates a new tuple and lets the
old one become invalid. It does this to keep the old tuple around
because a transaction that started before the update is supposed to see
the old tuple not the old one. Because a new tuple is created it has to
be inserted into all the indicies. If another database just has one
tuple and overwrites it it doesn't have to update the index.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com