Re: Advise about how to delete entries - Mailing list pgsql-performance

On Fri, Sep 02, 2005 at 01:43:05PM +0200, Arnau wrote:
>
>  statistic_id             | numeric(10,0)            | not null default
> nextval('STATISTICS_OPERATOR_ID_SEQ'::text)

Any reason this column is numeric instead of integer or bigint?

> That contains about 7.000.000 entries and I have to remove 33.000
> entries. I have created an sql file with all the delete sentences, e.g.:
>
>    "DELETE FROM statistics_sasme WHERE statistic_id = 9832;"
>
> then I do \i delete_items.sql. Remove a single entry takes more than 10
> seconds. What would you do to speed it up?

The referential integrity triggers might be slowing down the delete.
Do you have indexes on all foreign key columns that refer to this
table?  Do all foreign key columns that refer to statistic_id have
the same type as statistic_id (numeric)?  What's the output "EXPLAIN
ANALYZE DELETE ..."?  Do you vacuum and analyze the tables regularly?
What version of PostgreSQL are you using?

--
Michael Fuhr