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>
To: Gary Cowell <gary_cowell@yahoo.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: Major differences between oracle and postgres performance - what can I do ?
Date: Fri, 18 Jun 2004 14:16:03 -0400
Message-ID: <20040618181603.GO11196@ns.snowman.net> (raw)
In-Reply-To: <20040618175317.GN11196@ns.snowman.net>
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>
	<20040618175317.GN11196@ns.snowman.net>

* Stephen Frost (sfrost@snowman.net) wrote:
> 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.

Alright, after talking with some people on #postgresql I found that in
Oracle you can do 'set autotrace traceonly', which removes the I/O
factor from the Oracle query.  Doing this I also discovered that it
appears Oracle actually uses an index on that field that it knows about
to derive what the distinct results would be.  That probably invalidates
this test for what we were specifically looking for, but, hey, using the
index to figure out what the distinct values for the key are isn't
exactly a bad idea. :)

Here's the new results:

(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            

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 18 14:10:12 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


2322912 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11459 Card=1303962 B
          ytes=16951506)

   1    0   SORT* (ORDER BY) (Cost=11459 Card=1303962 Bytes=16951506)  :Q457001
   2    1     TABLE ACCESS* (FULL) OF 'P_GEN_DOM_DEDICATED_SWC_ACCESS' :Q457000
           (Cost=1550 Card=1303962 Bytes=16951506)



   1 PARALLEL_TO_SERIAL            SELECT A1.C0 C0 FROM :Q457000 A1 ORDER BY A1
                                   .C0

   2 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ACCESS
                                   _TYPE_ID" C0 FROM "P_GEN_DOM_DEDICAT



Statistics
----------------------------------------------------------
         32  recursive calls
       1594  db block gets
      64495  consistent gets
     105975  physical reads
          0  redo size
   40109427  bytes sent via SQL*Net to client
    1704111  bytes received via SQL*Net from client
     154862  SQL*Net roundtrips to/from client
          2  sorts (memory)
          4  sorts (disk)
    2322912  rows processed

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

real    1m38.55s
user    0m23.36s
sys     0m9.61s

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

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 18 14:13:54 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


16 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=44874 Card=1303962 B
          ytes=16951506)

   1    0   SORT (UNIQUE) (Cost=44874 Card=1303962 Bytes=16951506)
   2    1     INDEX (FAST FULL SCAN) OF 'TABLE_8111_DUPLICATE_CHECK' (
          UNIQUE) (Cost=4 Card=1303962 Bytes=16951506)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      47069  consistent gets
      47067  physical reads
          0  redo size
        841  bytes sent via SQL*Net to client
        662  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         16  rows processed

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

real    0m5.36s
user    0m0.04s
sys     0m0.07s
-----------------------------------------------------------------------------------

	Stephen

Attachments:

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

view thread (18+ messages)  latest in thread

Message-ID: <20040618181603.GO11196@ns.snowman.net>
Permalink:  ../20040618181603.GO11196@ns.snowman.net/
Also on:    postgresql.org/message-id/20040618181603.GO11196@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: <20040618181603.GO11196@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