Re: Delete Performance - Mailing list pgsql-general

Tom Lane wrote:


>
> I observed over in pg-hackers that deletion speed seems to be
> proportional to total volume of data deleted, but that's not enough
> to explain your results.  You're reporting a 10000X speed difference
> with only 10-100X difference in data volume, so there's still a large
> factor to be accounted for.
>
> Are you sure you don't have any rules, triggers, foreign keys involving
> the slower table?


Hmm, there is a foreign key defined in the "fast" table:

CREATE TABLE grib_catalog (
         edition           INTEGER NOT NULL CHECK(edition IN(1, 2)),
         discipline        INTEGER,
         generating_center INTEGER NOT NULL CHECK(generating_center
BETWEEN 7 AND 99),
         sub_center        INTEGER NOT NULL,
         scale_factor      INTEGER,
         grib_product_id   INTEGER REFERENCES grib_product,
         prod_category     INTEGER CHECK (prod_category BETWEEN 0 AND 19),
         grib_model_id     INTEGER REFERENCES grib_model,
         run_time          TIMESTAMP NOT NULL,
         fcst_time         INTEGER NOT NULL CHECK(fcst_time >= 0),
         grib_region_id    INTEGER REFERENCES grib_region,
         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,
         file_name         TEXT ,
         parse_time        TIMESTAMP ,
         gds_offset        INTEGER CHECK(gds_offset >= 0),
         pds_offset        INTEGER NOT NULL CHECK(pds_offset >= 0),
         drs_offset        INTEGER CHECK(drs_offset >= 0),
         ds_offset         INTEGER NOT NULL CHECK(ds_offset >= 0),
         bms_offset        INTEGER CHECK(bms_offset >= 0),
         PRIMARY
KEY(discipline,generating_center,sub_center,grib_product_id,grib_model_id,
         run_time,fcst_time,grib_region_id,level,bmp_usage,gds_usage),
         FOREIGN KEY (file_name,parse_time) REFERENCES grib_file
);

which results in pg_dump reporting an unnamed delete trigger.  I guess this
means that a delete on grib_file refers back to grib_catalog

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "grib_file"  FROM
"grib_catalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'grib_catalog',
'grib_file', 'UNSPECIFIED', 'file_name', 'name', 'parse_time',
'parse_time');

Will reformulate without the foreign key and see if this helps.

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