Re: How Postgresql Compares For Query And Load Operations

Mark kirkwood <markir@slingshot.co.nz> writes:
> I tried this query :

> SELECT sum(val) FROM fact0

> for Postgres, Db2 and Oracle. The results were

> Postgres    2m25s
> Db2        40s
> Oracle    50s

> This seems to be the likely culprit. I suspect that the "many
> block/page read at once" type optimzations (prefetch for Db2 and mutli
> block read for Oracle) mean that table sequential scans are faster for
> these guys than Postgres.

Hm.  The theory about simple sequential reads is that we expect the
kernel to optimize the disk access, since it'll recognize that we are
doing sequential access to the table file and do read-aheads.  Or that's
the theory, anyway.

I am not convinced that inefficient I/O is the story here.  We could be
paying the price of our very generalized implementation of aggregates.
It would be interesting to know how much CPU time was chewed up by each
DB during the SELECT sum().  It'd also be interesting to know just what
datatype is being summed.

            regards, tom lane