Re: Delete Performance
От | P.J. \"Josh\" Rovero |
---|---|
Тема | Re: Delete Performance |
Дата | |
Msg-id | 3BF91026.5000304@sonalysts.com обсуждение исходный текст |
Ответ на | Delete Performance ("P.J. \"Josh\" Rovero" <rovero@sonalysts.com>) |
Список | 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 ***********************************************************************
В списке pgsql-general по дате отправления: