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

* Stephen Frost (sfrost(at)snowman(dot)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