Looking for some hints on how to speed up deletes.... Thanks in advance
Using PostgreSQL 7.1.2 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(2 processors)
Have a "file" record wx_grib_file, with data stored as large object.
The rule "wx_grib_file_delete" does the lo_unlink on grib_file_id.
Table "wx_grib_file"
Attribute | Type | Modifier
--------------+-----------+----------
grib_file_id | oid | not null
name | char(40) |
parse_time | timestamp |
Index: wx_grib_file_pkey
Rule: wx_grib_file_delete
Triggers: RI_ConstraintTrigger_180325,
RI_ConstraintTrigger_180327
Have a catalog entry which uses the wx_grib_file key as a foreign key.
For referential integrity, the catalog entry must be deleted before the
file.
For refTable "wx_grib_catalog"
Attribute | Type | Modifier
-------------------+-----------+----------
edition | integer | not null
discipline | integer | not null
generating_center | integer | not null
sub_center | integer | not null
scale_factor | integer |
grib_product_id | integer | not null
prod_category | integer |
grib_model_id | integer | not null
run_time | timestamp | not null
fcst_time | integer | not null
grib_region_id | integer | not null
level | integer | not null
level_units | char(8) | not null
projection | char(16) | not null
bmp_usage | boolean | not null
wx_usage | boolean | not null
gds_usage | boolean | not null
grib_file_id | oid |
gds_offset | integer |
pds_offset | integer | not null
drs_offset | integer |
ds_offset | integer | not null
bms_offset | integer |
Index: wx_grib_catalog_pkey
Constraints: ((prod_category >= 0) AND (prod_category <= 19))
(fcst_time >= 0)
(gds_offset >= 0)
(pds_offset >= 0)
(drs_offset >= 0)
(ds_offset >= 0)
(bms_offset >= 0)
((edition = 1) OR (edition = 2))
((generating_center >= 7) AND (generating_center <= 99))
Inserts to these (including the lo_import) are very fast (5 to 10
sets per second), but deletes are very slow (1 or 2 secs per item).
Here are the plans:
explain verbose delete from wx_grib_catalog where grib_file_id = 3347194;
NOTICE: QUERY DUMP:
{ SEQSCAN :startup_cost 0.00 :total_cost 2787.20 :rows 1 :width 6
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk true } :expr { VAR :varno 1 :varattno -1 :vartype 27 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno -1}}) :qpqual ({ EXPR :typeOid
16 :opType op :oper { OPER :opno 1137 :opid 184 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 18 :vartype 26 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 18} { CONST :consttype 23
:constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 51 18
-6 ] })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:
explain verbose delete from wx_grib_file where grib_file_id = 3347194;
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1 :width 4
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname lo_unlink :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { EXPR :typeOid 23 :opType func :oper { FUNC
:funcid 964 :functype 23 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}}) :qpqual <>
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxid ( 180258) :indxqual (({ EXPR :typeOid 16 :opType
op :oper { OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxqualorig (({
EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 0 51 18 -6 ] })})) :indxorderdir 1 }
NOTICE: QUERY PLAN:
Index Scan using wx_grib_file_pkey on wx_grib_file (cost=0.00..3.10
rows=1 width=4)
NOTICE: QUERY DUMP:
{ NESTLOOP :startup_cost 0.00 :total_cost 3698.46 :rows 1 :width 14
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk true } :expr { VAR :varno 65000 :varattno 1 :vartype 27
:vartypmod -1 :varlevelsup 0 :varnoold 6 :varoattno -1}}) :qpqual <>
:lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1 :width
4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 26
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 26 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:indxid ( 180258) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxqualorig (({
EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 0 51 18 -6 ] })})) :indxorderdir 1 } :righttree {
SEQSCAN :startup_cost 0.00 :total_cost 2560.16 :rows 90816 :width 10
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 6 :varattno -1 :vartype 27
:vartypmod -1 :varlevelsup 0 :varnoold 6 :varoattno -1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 26 :restypmod -1 :resname <> :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 6
:varattno 18 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 6
:varoattno 18}}) :qpqual <> :lefttree <> :righttree <> :extprm ()
:locprm () :initplan <> :nprm 0 :scanrelid 6 } :extprm () :locprm ()
:initplan <> :nprm 0 :jointype 0 :joinqual ({ EXPR :typeOid 16 :opType
op :oper { OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR
:varno 65000 :varattno 2 :vartype 26 :vartypmod -1 :varlevelsup 0
:varnoold 6 :varoattno 18} { VAR :varno 65001 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})})}
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..3698.46 rows=1 width=14)
-> Index Scan using wx_grib_file_pkey on wx_grib_file
(cost=0.00..3.10 rows=1 width=4)
-> Seq Scan on wx_grib_catalog (cost=0.00..2560.16 rows=90816 width=10)
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1 :width 6
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk true } :expr { VAR :varno 1 :varattno -1 :vartype 27 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno -1}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:indxid ( 180258) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxqualorig (({
EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 0 51 18 -6 ] })})) :indxorderdir 1 }
NOTICE: QUERY PLAN:
Index Scan using wx_grib_file_pkey on wx_grib_file (cost=0.00..3.10
rows=1 width=6)
EXPLAIN
--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************
