Re: How Postgresql Compares For Query And Load Operations

Mark kirkwood <markir@slingshot.co.nz> writes:
> Postgres 7.1.2 executed this query like :

>  Aggregate  (cost=2732703.88..2738731.49 rows=120552 width=20)
>   -> Group  (cost=2732703.88..2735717.69 rows=1205521 width=20)
>       -> Sort  (cost=2732703.88..2732703.88 rows=1205521 width=20)
>          -> Hash Join  (cost=1967.52..2550188.93 rows=1205521 width=20)
>            -> Seq Scan on fact1 f  (cost=0.00..1256604.00 rows=3000000
> width=8)
>            -> Hash  (cost=1957.47..1957.47 rows=4018 width=12)
>              -> Index Scan using dim0_q1 on dim0 d0  (cost=0.00..1957.47
> rows=4018 width=12)

> for an elapsed time of 3m50s

> Wheras Oracle 9.0 used :

>  SELECT STATEMENT Optimizer=CHOOSE (Cost=5810 Card=4020 Bytes =60300)
>      SORT (GROUP BY) (Cost=5810 Card=4020 Bytes=60300)
>        HASH JOIN (Cost=5810 Card=2989644 Bytes=44844660)
>          TABLE ACCESS (FULL) OF 'DIM0' (Cost=4 Card=4020 Bytes= 40200)
>          TABLE ACCESS (FULL) OF 'FACT1' (Cost=5806 Card=2990089
> Bytes=14950445)

> for an elapsed time of 50s.

> 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 out the dim0 rows that don't meet
    d0.f1 between '2007-07-01' AND '2018-07-01'
before they get loaded into the hashtable, whereas Oracle is just
scanning dim0 by brute force (and presumably evaluating the BETWEEN
clause directly at each row).  Given that that's the much smaller table,
it's unlikely that that makes much difference.  I am assuming that the
different order in which the hash-join's inputs are listed is just an
artifact of the listing format --- surely Oracle is also choosing to
load the dim0 rows into an in-memory hash table and then scan fact1
to probe the hash table, rather than trying to load all of fact1 into
memory.

My guess is that the Oracle plan is misleading where it says "SORT",
and that they are actually doing no such thing as a sort.  Given that
they only expect 4000 distinct rows out of the grouping/aggregation
steps, it'd make more sense to do the group+aggregation in one pass
using another in-memory hash table (indexed by GROUP BY key, with
contents being the count() accumulator).  Currently, Postgres only knows
how to form groups via a true sort and then a "GROUP" pass (works like
the traditional "sort | uniq" Unix filtering method).  But if you
actually want to aggregate the groups, and there aren't too many of
them, then you can form each aggregate in parallel in one unsorted pass
over the input, keeping the aggregate intermediate results in entries in
a hash table.  Hash-based aggregation is on our TODO list, and is
fairly high priority in my eyes (though I doubt it'll be done for 7.2).

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?


> Buld Load times for a 3000000 row (700Mb ) fact table were

> Postgresql    9m30s    (copy)
> Db2        2m15s    (load)
> Oracle        5m    (sqlldr)
> Mysql        2m20s    (load)

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