Re: Major differences between oracle and postgres performance - what can I do ? - Mailing list pgsql-performance

* 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