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