Re: How Postgresql Compares For Query And Load Operations

On Saturday 14 July 2001 02:49, Tom Lane wrote:

> >
> > It would seem that Oracle's execution plan is more optimal.
>
> Hmm, since I don't know the details of Oracle's plan displays, it's hard
> to comment on that --- but it looks to me like the plans are essentially
> the same, with the small difference that Postgres chooses to use the
> index on dim0 to filter ....(snipped )

After a little thinking, I am inclined to agree with you Tom... I wondered if
the difference might to be due to pure sequential scan performance
differences. 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.

Thus on the bright side their access plans are not necessarily any better
than Postgres !

regards

Mark