Re: Delete Performance

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(at)sonalysts(dot)com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************