glacial deletes (long) - Mailing list pgsql-general

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
***********************************************************************