Re: execution plan : Oracle vs PostgreSQL

here is the EXPLAIN ANALYSE of my query :

   {SORT
   :startup_cost 9.65
   :total_cost 9.66
   :plan_rows 1
   :plan_width 28
   :targetlist (
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 1
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 1
         }
      :resno 1
      :resname child_id
      :ressortgroupref 0
      :resorigtbl 34719
      :resorigcol 1
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 2
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 2
         }
      :resno 2
      :resname evolution
      :ressortgroupref 1
      :resorigtbl 34719
      :resorigcol 2
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 3
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 3
         }
      :resno 3
      :resname isremoved
      :ressortgroupref 0
      :resorigtbl 34719
      :resorigcol 3
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 4
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 4
         }
      :resno 4
      :resname child_class
      :ressortgroupref 0
      :resorigtbl 34719
      :resorigcol 4
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 5
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 5
         }
      :resno 5
      :resname indx
      :ressortgroupref 2
      :resorigtbl 34719
      :resorigcol 5
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 6
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 6
         }
      :resno 6
      :resname ele_id
      :ressortgroupref 0
      :resorigtbl 34719
      :resorigcol 6
      :resjunk false
      }
      {TARGETENTRY
      :expr
         {VAR
         :varno 1
         :varattno 7
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 7
         }
      :resno 7
      :resname doc_id
      :ressortgroupref 0
      :resorigtbl 34719
      :resorigcol 7
      :resjunk false
      }
   )
   :qual <>
   :lefttree
      {INDEXSCAN
      :startup_cost 0.00
      :total_cost 9.64
      :plan_rows 1
      :plan_width 28
      :targetlist (
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 1
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 1
            }
         :resno 1
         :resname child_id
         :ressortgroupref 0
         :resorigtbl 34719
         :resorigcol 1
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 2
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 2
            }
         :resno 2
         :resname evolution
         :ressortgroupref 1
         :resorigtbl 34719
         :resorigcol 2
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 3
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 3
            }
         :resno 3
         :resname isremoved
         :ressortgroupref 0
         :resorigtbl 34719
         :resorigcol 3
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 4
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 4
            }
         :resno 4
         :resname child_class
         :ressortgroupref 0
         :resorigtbl 34719
         :resorigcol 4
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 5
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 5
            }
         :resno 5
         :resname indx
         :ressortgroupref 2
         :resorigtbl 34719
         :resorigcol 5
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 6
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 6
            }
         :resno 6
         :resname ele_id
         :ressortgroupref 0
         :resorigtbl 34719
         :resorigcol 6
         :resjunk false
         }
         {TARGETENTRY
         :expr
            {VAR
            :varno 1
            :varattno 7
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 7
            }
         :resno 7
         :resname doc_id
         :ressortgroupref 0
         :resorigtbl 34719
         :resorigcol 7
         :resjunk false
         }
      )
      :qual (
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 2
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 2
            }
            {SUBPLAN
            :subLinkType 4
            :useOr false
            :exprs <>
            :paramIds <>
            :plan
               {AGG
               :startup_cost 4.93
               :total_cost 4.94
               :plan_rows 1
               :plan_width 4
               :targetlist (
                  {TARGETENTRY
                  :expr
                     {AGGREF
                     :aggfnoid 2116
                     :aggtype 23
                     :target
                        {VAR
                        :varno 0
                        :varattno 2
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 2
                        }
                     :agglevelsup 0
                     :aggstar false
                     :aggdistinct false
                     }
                  :resno 1
                  :resname max
                  :ressortgroupref 0
                  :resorigtbl 0
                  :resorigcol 0
                  :resjunk false
                  }
               )
               :qual <>
               :lefttree
                  {INDEXSCAN
                  :startup_cost 0.00
                  :total_cost 4.93
                  :plan_rows 1
                  :plan_width 4
                  :targetlist (
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 1
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 1
                        }
                     :resno 1
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 2
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 2
                        }
                     :resno 2
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 3
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 3
                        }
                     :resno 3
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 4
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 4
                        }
                     :resno 4
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 5
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 5
                        }
                     :resno 5
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 6
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 6
                        }
                     :resno 6
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                     {TARGETENTRY
                     :expr
                        {VAR
                        :varno 1
                        :varattno 7
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 7
                        }
                     :resno 7
                     :resname <>
                     :ressortgroupref 0
                     :resorigtbl 0
                     :resorigcol 0
                     :resjunk false
                     }
                  )
                  :qual (
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 4
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 4
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 3
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                  )
                  :lefttree <>
                  :righttree <>
                  :initPlan <>
                  :extParam (b 0 1 2 3)
                  :allParam (b 0 1 2 3)
                  :nParamExec 0
                  :scanrelid 1
                  :indexid 34737
                  :indexqual (
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 1
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 7
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 0
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 2
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 6
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 1
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                     {OPEXPR
                     :opno 523
                     :opfuncid 149
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 4
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 2
                        }
                        {CONST
                        :consttype 23
                        :constlen 4
                        :constbyval true
                        :constisnull false
                        :constvalue 4 [ 0 0 0 0 ]
                        }
                     )
                     }
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 5
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 1
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 2
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                  )
                  :indexqualorig (
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 7
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 7
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 0
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 6
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 6
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 1
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                     {OPEXPR
                     :opno 523
                     :opfuncid 149
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 2
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 2
                        }
                        {CONST
                        :consttype 23
                        :constlen 4
                        :constbyval true
                        :constisnull false
                        :constvalue 4 [ 0 0 0 0 ]
                        }
                     )
                     }
                     {OPEXPR
                     :opno 96
                     :opfuncid 65
                     :opresulttype 16
                     :opretset false
                     :args (
                        {VAR
                        :varno 1
                        :varattno 1
                        :vartype 23
                        :vartypmod -1
                        :varlevelsup 0
                        :varnoold 1
                        :varoattno 1
                        }
                        {PARAM
                        :paramkind 15
                        :paramid 2
                        :paramname <>
                        :paramtype 23
                        }
                     )
                     }
                  )
                  :indexstrategy (i 3 3 2 3)
                  :indexsubtype (o 0 0 0 0)
                  :indexorderdir 1
                  }
               :righttree <>
               :initPlan <>
               :extParam (b 0 1 2 3)
               :allParam (b 0 1 2 3)
               :nParamExec 0
               :aggstrategy 0
               :numCols 0
               :numGroups 0
               }
            :plan_id 1
            :rtable (
               {RTE
               :alias
                  {ALIAS
                  :aliasname c2
                  :colnames <>
                  }
               :eref
                  {ALIAS
                  :aliasname c2
                  :colnames ("child_id" "evolution" "isremoved" "child_class"
                  "indx" "ele_id" "doc_id")
                  }
               :rtekind 0
               :relid 34719
               :inh false
               :inFromCl true
               :requiredPerms 2
               :checkAsUser 0
               }
            )
            :useHashTable false
            :unknownEqFalse false
            :setParam <>
            :parParam (i 0 1 2 3)
            :args (
               {VAR
               :varno 1
               :varattno 7
               :vartype 23
               :vartypmod -1
               :varlevelsup 0
               :varnoold 1
               :varoattno 7
               }
               {VAR
               :varno 1
               :varattno 6
               :vartype 23
               :vartypmod -1
               :varlevelsup 0
               :varnoold 1
               :varoattno 6
               }
               {VAR
               :varno 1
               :varattno 1
               :vartype 23
               :vartypmod -1
               :varlevelsup 0
               :varnoold 1
               :varoattno 1
               }
               {VAR
               :varno 1
               :varattno 4
               :vartype 23
               :vartypmod -1
               :varlevelsup 0
               :varnoold 1
               :varoattno 4
               }
            )
            }
         )
         }
      )
      :lefttree <>
      :righttree <>
      :initPlan <>
      :extParam (b)
      :allParam (b)
      :nParamExec 0
      :scanrelid 1
      :indexid 34737
      :indexqual (
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 1
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 7
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 0 100 ]
            }
         )
         }
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 2
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 6
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 10 29 ]
            }
         )
         }
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 3
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 3
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 0 0 ]
            }
         )
         }
      )
      :indexqualorig (
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 7
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 7
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 0 100 ]
            }
         )
         }
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 6
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 6
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 10 29 ]
            }
         )
         }
         {OPEXPR
         :opno 96
         :opfuncid 65
         :opresulttype 16
         :opretset false
         :args (
            {VAR
            :varno 1
            :varattno 3
            :vartype 23
            :vartypmod -1
            :varlevelsup 0
            :varnoold 1
            :varoattno 3
            }
            {CONST
            :consttype 23
            :constlen 4
            :constbyval true
            :constisnull false
            :constvalue 4 [ 0 0 0 0 ]
            }
         )
         }
      )
      :indexstrategy (i 3 3 3)
      :indexsubtype (o 0 0 0)
      :indexorderdir 1
      }
   :righttree <>
   :initPlan <>
   :extParam (b)
   :allParam (b)
   :nParamExec 4
   :numCols 2
   :sortColIdx 2 5
   :sortOperators 97 97
   }

Sort  (cost=9.65..9.66 rows=1 width=28) (actual time=0.190..0.191 rows=1 loops=1)
  Sort Key: evolution, indx
  ->  Index Scan using index_xdb_child on xdb_child c1  (cost=0.00..9.64 rows=1 width=28) (actual time=0.156..0.159
rows=1loops=1) 
        Index Cond: ((doc_id = 100) AND (ele_id = 2589) AND (isremoved = 0))
        Filter: (evolution = (subplan))
        SubPlan
          ->  Aggregate  (cost=4.93..4.94 rows=1 width=4) (actual time=0.053..0.053 rows=1 loops=1)
                ->  Index Scan using index_xdb_child on xdb_child c2  (cost=0.00..4.93 rows=1 width=4) (actual
time=0.026..0.034rows=1 loops=1) 
                      Index Cond: ((doc_id = $0) AND (ele_id = $1) AND (evolution <= 0) AND (child_id = $2))
                      Filter: (child_class = $3)
Total runtime: 0.483 ms

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de FERREIRA,
William (VALTECH)
Envoyé : mardi 24 janvier 2006 11:34
À : pgsql-general@postgresql.org
Objet : [GENERAL] execution plan : Oracle vs PostgreSQL




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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly


This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.


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.