pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How Postgresql Compares For Some Query Types
3+ messages / 3 participants
[nested] [flat]

* How Postgresql Compares For Some Query Types
@ 2001-07-12 11:50  Mark kirkwood <markir@slingshot.co.nz>
  0 siblings, 1 reply; 3+ messages in thread

From: Mark kirkwood @ 2001-07-12 11:50 UTC (permalink / raw)
  To: pgsql-sql

Dear list,

With the advent of Version 7.1.2 I thought it would be interesting to compare 
how Postgresql does a certain class of queries (Star Queries), and Data Loads 
with some of the other leading databases ( which were in my humble opinion 
Mysql 3.23.39, Db2 7.2 ,Oracle 9.0 [ ok - Possibly SQL 2000 too but I dont 
run Winanyk] ). 

The results were overall very encouraging :

Postgresql can clearly hold its own when compared to the "big guys".

The full details (together with a wee rant) are aviailable on :

http://homepages.slingshot.co.nz/~markir

(if anyone asks I can submit the entire results...but I figured, lets cut to 
the chase here....)

There were two areas where Postgresql was slower, and I thought it would be 
informative to discuss these briefly :


1  Star query scanning a sigificant portion of a fact table 

SELECT 
       d0.f1,
       count(f.val)
FROM dim0 d0,
     fact1 f
WHERE d0.d0key = f.d0key
AND   d0.f1 between '2007-07-01' AND '2018-07-01'
GROUP BY d0.f1

This query requires summarizing a significant proportion of the 3000000 row ( 
700Mb ) fact1 table.

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.


2	Bulk loading data

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

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


(Note that Db2 is faster than Mysql here ! )

While I left "fsync = on" for this test... I still think the difference was 
worth noting.

Any comments on these sort of results would be welcome.

regards

Mark







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How Postgresql Compares For Some Query Types
@ 2001-07-17 15:18  Josh Berkus <josh@agliodbs.com>
  parent: Mark kirkwood <markir@slingshot.co.nz>
  0 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-07-17 15:18 UTC (permalink / raw)
  To: Mark kirkwood <markir@slingshot.co.nz>; pgsql-sql

Mark, (and Tom below)

	Thanks for doing the tests.  These'll be useful to me if any client
asks me again if PostgreSQL is a "real" database.  I'm particularly
intrigued that MySQL performed so poorly.  In the field, it's been quite
speedy but maybe that's because applications using it are restricted to
simple queries.

	You may wish to post your link to Slashdot and pgsql-announce as well.
I'm sure the Great Bridge folks will be interested.

	I'm a little unclear on what a "fact table" is.  Can you explain?

	Finally, Tom, I figured out what was up with the 30-45 second response
time on the large star query I couldn't get to perform.  Turns out that
the client had changed two of the VARCHAR fields to TEXT fields and
added large blocks of text (> 16k for some) to them.  When I removed
these fields from the query, it gives me the <7s response time I'd
expected.


							-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How Postgresql Compares For Some Query Types
@ 2001-07-18 23:02  markir@slingshot.co.nz
  0 siblings, 0 replies; 3+ messages in thread

From: markir@slingshot.co.nz @ 2001-07-18 23:02 UTC (permalink / raw)
  To: josh@agliodbs.com; Mark kirkwood <markir@slingshot.co.nz>; pgsql-sql


> 
> 	I'm a little unclear on what a "fact table" is.  Can you explain?

Good question... The idea comes from data warehousing where a typical data 
construction involves two "types" of table :

dimensions : time, location, ethicity....( essentially denormalized lookups )
facts      : observations, accidents, sales ....

facts have a composite primary key , each componant of which is a foreign key 
for one of the dimensions.

If drawn with the fact table(s) in the center and dimensions around about, then 
the ERD looks like a "star". Hence the names "star schema" and "star query"

A quick search located a vaguely helpful page : ( apologies about the source )

http://www.oradoc.com/ora816/server.816/a76994/schemas.htm

A much better source is Kimball's book "The Data Warehousing Toolkit".

regards

Mark









^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-18 23:02 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-12 11:50 How Postgresql Compares For Some Query Types Mark kirkwood <markir@slingshot.co.nz>
2001-07-17 15:18 ` Josh Berkus <josh@agliodbs.com>
2001-07-18 23:02 Re: How Postgresql Compares For Some Query Types markir@slingshot.co.nz

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox