Thread: Delete Performance

Tom Lane suggested I look at EXPLAIN output, which showed
  that both the catalog (fast delete case) and toasted text
table (slow delete case) were using sequential scans when
deleting any significant number of records.\

But even with sequential scan, the catalog entries are
deleted quickly (30K records in just a couple of seconds),
vice slow deletes (2 per second) for the toasted text.

The catalog entries are about 200 bytes (integers, timestamps,
a couple of short fixed length strings), while the toasted
text table has one short text field, one timestamp, and one
long (2K to 20K bytes) toasted text field.

Both will use index scans when a very small number (< 1%)
of records would be selected.  But relative delete performance
stays the same.
--
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
***********************************************************************

"P.J. \"Josh\" Rovero" <rovero@sonalysts.com> writes:
> But even with sequential scan, the catalog entries are
> deleted quickly (30K records in just a couple of seconds),
> vice slow deletes (2 per second) for the toasted text.

> The catalog entries are about 200 bytes (integers, timestamps,
> a couple of short fixed length strings), while the toasted
> text table has one short text field, one timestamp, and one
> long (2K to 20K bytes) toasted text field.

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?

            regards, tom lane

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