Re: automated row deletion - Mailing list pgsql-general

A colleague gave me the following query to run:

DELETE FROM data_log_20msec_table WHERE (log_id IN (SELECT log_id FROM data_log_20msec_table ORDER BY log_id DESC OFFSET 10000000))

log_id is the primary key (big serial)

data_log is the table described below

This query keeps the most recent 10 million rows and deletes the remaining ones. If I call this once a minute, it would be deleting 3000 rows each time. Is there a way to optimize this statement? Postgres was setup with default configuration. Is there anything we can change in the configuration to make this run more efficiently? The table is defined as below:

CREATE TABLE data_log_20msec_table

(

  log_id bigserial NOT NULL,

  timestamp_dbl double precision,

  data bytea,

  CONSTRAINT data_log_20msec_table_pkey PRIMARY KEY (log_id)

)

WITH (OIDS=FALSE);

ALTER TABLE data_log_20msec_table OWNER TO postgres;

-- Index: data_log_20msec_table_timestamp_index

-- DROP INDEX data_log_20msec_table_timestamp_index;

CREATE INDEX data_log_20msec_table_timestamp_index

  ON data_log_20msec_table

  USING btree

  (timestamp_dbl);

Is there anything we can do here that can optimize the deletion of rows?

Much thanks to anyone who can help us out.

Regards,

Dave