execution plan : Oracle vs PostgreSQL - Mailing list pgsql-general

hi,

i have a database storing XML documents.
The main table contains the nodes of the document, the other tables contain data for each node (depending on the node's
type: ELE, Text, PI, ...) 

My test document has 115000 nodes.
the export of the document(extracting all informations from database and writing XML file on disk) takes 30s with
Oracleand 5mn with Postgresql. 
The tests have been made on different systems
- Sun systems :
    - solaris8 : 16 cpu and 64Gb RAM
    - solaris8 : 2cpu and 8Gb RAM
- Windows Systems :
    - WinNT : 1 cpu(PIV) and 1Gb RAM
    - WinXP : 1 cpu(centrino) and 512 RAM

the times are always the same, except with the centrino for which it takes 1 min.

So i don't understand such differences.

here is my main query (on the main table for getting the children of a node) and the execution plan for PostgreSQL and
Oracle: 

-Query :
SELECT   *
    FROM xdb_child c1
   WHERE c1.doc_id = 100
     AND c1.ele_id = 2589
     AND c1.isremoved = 0
     AND c1.evolution =
            (SELECT
                    MAX (evolution)
               FROM xdb_child c2
              WHERE c2.doc_id = c1.doc_id
                AND c2.ele_id = c1.ele_id
                AND c2.evolution <= 0
                AND c2.child_id = c1.child_id
                AND c2.child_class = c1.child_class) ORDER BY c1.evolution, c1.indx

-Postgresql plan :
"Sort  (cost=33.19..33.20 rows=1 width=28)"
"  Sort Key: evolution, indx"
"  ->  Index Scan using index_xdb_child_1 on xdb_child c1  (cost=0.00..33.18 rows=1 width=28)"
"        Index Cond: ((ele_id = 2589) AND (doc_id = 100) AND (isremoved = 0))"
"        Filter: (evolution = (subplan))"
"        SubPlan"
"          ->  Result  (cost=4.34..4.35 rows=1 width=0)"
"                InitPlan"
"                  ->  Limit  (cost=0.00..4.34 rows=1 width=4)"
"                        ->  Index Scan Backward using index_xdb_child_2 on xdb_child c2  (cost=0.00..4.34 rows=1
width=4)"
"                              Index Cond: ((ele_id = $1) AND (doc_id = $0) AND (child_id = $2) AND (child_class = $3)
AND(evolution <= 0))" 
"                              Filter: (evolution IS NOT NULL)"

-Oracle plan (cost 14):
Operation    Object Name    Rows    Bytes    Cost    Object Node    In/Out    PStart    PStop

SELECT STATEMENT Optimizer Mode=CHOOSE        1           14
  SORT ORDER BY        1      4 K    14
    TABLE ACCESS BY INDEX ROWID    XDB_CHILD    1      4 K    4
      INDEX RANGE SCAN    INDEX_XDB_CHILD_1    1           3
        SORT AGGREGATE        1      65
          FIRST ROW        1      65      3
            INDEX RANGE SCAN (MIN/MAX)    INDEX_XDB_CHILD_2    8 M         3

Regards

    William


This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.