Re: How Postgresql Compares For Query And Load Operations

Tom,

Good point....

I notice I have set Oracle "pga_aggregate_size=30M" which includes the sort
space for a user process ( oops) whereas every other db has about 2M ( there
are a few typos on the page... forgot to update from an earlier study)

I will have to re-run the Oracle results with 2M ( or re-run the rest with
30M...) I will update you .....

> If Oracle really is doing a sort, it's hard to see where the speed
> difference came from --- unless you have set the tuning parameters such
> that Oracle does the sort all-in-memory whereas Postgres doesn't.  Sorts
> that have to go to disk are lots slower.

> Can anyone who actually knows how to read Oracle plans confirm or deny
> these speculations?

I will have a play with a clearer example for the star optimization business
( the thoery being - I believe ...that for a star query with n (small)
dimension tables and 1 (big) fact table, it is best to cartesian product the
dimensions, determine a set of keys, and access to the fact table using
these). My "trivial" example with 1 dimension does not illustrate this that
well...( I have another with 2 dimension tables which should be
better)...again I will update you.
>
> > Buld Load times for a 3000000 row (700Mb ) fact table were
> >
> > Postgresql    9m30s    (copy)
> > Db2        2m15s    (load)
> > Oracle        5m    (sqlldr)
> > Mysql        2m20s    (load)
>

There are a few "optional" scripts in the tar - which I should have indicated
:-( ... I do not do the cluster, primary or foreign keys at all ( there were
too many variations and options for constraints for all the different
databases)....so I just create the table, load via copy and then create the
various indexes. The load timings are for the fact0 table with no indexes
created.

> Hmm, I couldn't make out from your webpage exactly how you did the
> loading, or which steps are included in your timings.  I see that you
> used COPY, which is good ... but did you create the indexes before or
> after COPY?  What about the constraints?  I also see a CLUSTER script
> --- was this used, and if so where is its time counted?
>
>             regards, tom lane