From: Michael Fuhr <mike@fuhr.org>
To: Arnau <arnaulist@andromeiberica.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Advise about how to delete entries
Date: Fri, 2 Sep 2005 07:36:10 -0600
Message-ID: <20050902133610.GB65294@winnie.fuhr.org> (raw)
In-Reply-To: <43183AC9.1060704@andromeiberica.com>
References: <43183AC9.1060704@andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, arnaulist@andromeiberica.com
Subject: Re: Advise about how to delete entries
In-Reply-To: <20050902133610.GB65294@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox