pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gary Cowell <gary_cowell@yahoo.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Major differences between oracle and postgres performance - what can I do ?
Date: Fri, 18 Jun 2004 13:53:17 -0400
Message-ID: <20040618175317.GN11196@ns.snowman.net> (raw)
In-Reply-To: <22514.1087578111@sss.pgh.pa.us>
References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com>
	<20412.1087570627@sss.pgh.pa.us>
	<20040618163003.GM11196@ns.snowman.net>
	<22514.1087578111@sss.pgh.pa.us>

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> [... thinks for awhile ...]  It seems possible that they may use sort
> >> code that knows it is performing a DISTINCT operation and discards
> >> duplicates on sight.  Given that there are only 534 distinct values,
> >> the sort would easily stay in memory if that were happening.
> 
> > Could this optimization be added to PostgreSQL?  It sounds like a very
> > reasonable thing to do.
> 
> That's what I was wondering about too.  But first I'd like to get
> some kind of reading on how effective it would be.  If someone can
> demonstrate that Oracle can do sort-and-drop-dups a lot faster than
> it can do a straight sort of the same amount of input data, that
> would be a strong indication that it's worth doing.  At this point
> we don't know if that's the source of their win or not.

Alright, I did a couple tests, these are different systems with
different hardware, but in the end I think the difference is clear:

tsf=# explain analyze select distinct access_type_id from p_gen_dom_dedicated_swc_access ;
                                                                       QUERY PLAN                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------                               
 Unique  (cost=321591.00..333205.56 rows=16 width=10) (actual time=32891.141..37420.429 rows=16 loops=1)
   ->  Sort  (cost=321591.00..327398.28 rows=2322912 width=10) (actual time=32891.137..35234.810 rows=2322912 loops=1)
         Sort Key: access_type_id
         ->  Seq Scan on p_gen_dom_dedicated_swc_access  (cost=0.00..55492.12 rows=2322912 width=10) (actual time=0.013..3743.470 rows=2322912 loops=1)
 Total runtime: 37587.519 ms
(5 rows)

tsf=# explain analyze select access_type_id from p_gen_dom_dedicated_swc_access order by access_type_id;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=321591.00..327398.28 rows=2322912 width=10) (actual time=32926.696..35278.847 rows=2322912 loops=1)
   Sort Key: access_type_id     
   ->  Seq Scan on p_gen_dom_dedicated_swc_access  (cost=0.00..55492.12 rows=2322912 width=10) (actual time=0.014..3753.443 rows=2322912 loops=1)
 Total runtime: 36737.628 ms    
(4 rows)                        

So, about the same from postgres in each case.  From Oracle:

(select access_type_id from p_gen_dom_dedicated_swc_access order by access_type_id)
sauron:/home/sfrost> time sqlplus mci_vendor/mci @test.sql > /dev/null

real    3m55.12s
user    2m25.87s
sys     0m10.59s

(select distinct access_type_id from p_gen_dom_dedicated_swc_access)
sauron:/home/sfrost> time sqlplus mci_vendor/mci @test.sql > /dev/null

real    0m5.08s
user    0m3.86s
sys     0m0.95s

All the queries were run multiple times, though there wasn't all that
much difference in the times.  Both systems are pretty speedy, but I
tend to feel the Postgres box is faster in CPU/disk access time, which
is probably why the Oracle system took 4 minutes to do what the Postgres
systems does in 40 seconds.  My only other concern is the Oracle system
having to do the write I/O while the postgres one doesn't...  I don't
see an obvious way to get around that though, and I'm not sure if it'd
really make *that* big of a difference.

	Stephen

Attachments:

  signature.asc (application/pgp-signature, 188B) download

view thread (18+ messages)  latest in thread

Message-ID: <20040618175317.GN11196@ns.snowman.net>
Permalink:  ../20040618175317.GN11196@ns.snowman.net/
Also on:    postgresql.org/message-id/20040618175317.GN11196@ns.snowman.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: sfrost@snowman.net, tgl@sss.pgh.pa.us, gary_cowell@yahoo.co.uk
  Subject: Re: Major differences between oracle and postgres performance - what can I do ?
  In-Reply-To: <20040618175317.GN11196@ns.snowman.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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