Re: How Postgresql Compares For Query And Load Operations

> > 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

I monitored the cpu consumed by the relevant db processes ( counting the time
noted against each process from ps -ef, hope that was what you had in mind )

DB Elapsed Cpu
Postgres 2m25s 2m01s
Db2 50s 30s
Oracle 40s 18s

( I seem to have got my numbers for Db2 and the big O around the wrong way in
the last post ! )

I thought it was worth trying a different query as well :

SELECT count(*) FROM fact0

DB Elapsed Cpu
Postgres 1m5s 32s
Db2 23s 15s
Oracle 37s 11s

Finally the datatypes etc for the table

Table "fact0"
Attribute | Type | Modifier
-----------+---------+----------
d0key | integer |
d1key | integer |
d2key | integer |
val | integer |
filler | text |
Index: fact0_pk

In terms of caching etc.... the first query was run from a cold start, the
second immediatly afterwards.

The Postgres db has 4000 (8K) pages of data buffers and the table itself is
57000 pages. ( others were configured analagously )

regards

Mark