execution plan : Oracle vs PostgreSQL

Поиск
Список
Период
Сортировка
От FERREIRA, William (VALTECH)
Тема execution plan : Oracle vs PostgreSQL
Дата
Msg-id 414D259CE29DE54DAD534037C83CE4B726961D@FR0-MAILMB20.res.airbus.corp
обсуждение исходный текст
Ответы Re: execution plan : Oracle vs PostgreSQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
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 Oracle stored procedure is written in pl/sql and the Postgresql stored procedure in pl/perl (using spi_exec).
The export stored procedure use a SAX way algorithm : from a node, get all the children and for each child if it's an
Elementgo in recursion else write data into a file. 

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

-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

-PostgreSQL explain analyse :
   {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.163..0.164 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.133..0.135
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.048..0.048 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.025..0.030rows=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.418 ms

the Postgresql cost is better but the query is two times slower.

an other question about the procedural language : is pl/perl efficient with a such process knowing that it's just a
testdocument : a real document contains between 1 and 3 millions of nodes. 


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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: partitioning and locking problems
Следующее
От: Michael Stone
Дата:
Сообщение: Re: Huge Data sets, simple queries