Thread: Postgres Internals

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've tried to look around for this on the net, but all i seem to get is
a big arguement consisting of the features lacking in mysql that are in
other databases e.g. ACID!!

Thanks,

Dipesh

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@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com