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 Oracle and 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 Element go 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=1 loops=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.030 rows=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 test document : 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.
