Thread: Advise about how to delete entries
Hi all,
I have the following table:
espsm_asme=# \d statistics_sasme
Table "public.statistics_sasme"
Column | Type |
Modifiers
--------------------------+--------------------------+--------------------------------------------------------------
statistic_id | numeric(10,0) | not null default
nextval('STATISTICS_OPERATOR_ID_SEQ'::text)
input_message_id | character varying(50) |
timestamp_in | timestamp with time zone |
telecom_operator_id | numeric(4,0) |
enduser_number | character varying(15) | not null
telephone_number | character varying(15) | not null
application_id | numeric(10,0) |
customer_id | numeric(10,0) |
customer_app_config_id | numeric(10,0) |
customer_app_contents_id | numeric(10,0) |
message | character varying(160) |
message_type_id | numeric(4,0) |
Indexes:
"pk_stsasme_statistic_id" primary key, btree (statistic_id)
Triggers:
"RI_ConstraintTrigger_17328735" AFTER INSERT OR UPDATE ON
statistics_sasme FROM telecom_operators NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('fk_stsasme_telecom_operator_id',
'statistics_sasme', 'telecom_operators', 'UNSPECIFIED',
'telecom_operator_id', 'telecom_operator_id')
"RI_ConstraintTrigger_17328738" AFTER INSERT OR UPDATE ON
statistics_sasme FROM applications NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('fk_stsasme_application_id', 'statistics_sasme',
'applications', 'UNSPECIFIED', 'application_id', 'application_id')
"RI_ConstraintTrigger_17328741" AFTER INSERT OR UPDATE ON
statistics_sasme FROM customers NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('fk_stsasme_customer_id',
'statistics_sasme', 'customers', 'UNSPECIFIED', 'customer_id',
'customer_id')
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?
Thank you very much
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
> "DELETE FROM statistics_sasme WHERE statistic_id = 9832;"
As Michael said, why use a NUMERIC when a bigint is faster and better for
your use case, as you only need an integer and not a fixed precision
decimal ?
Also if you use postgres < 8, the index will not be used if you search on
a type different from the column type. So, if your key is a bigint, you
should do WHERE statistic_id = 9832::bigint.
For mass deletes like this, you should use one of the following, which
will be faster :
DELETE FROM ... WHERE ID IN (list of values)
Don't put the 30000 values in the same query, but rather do 300 queries
with 100 values in each.
COPY FROM a file with all the ID's to delete, into a temporary table, and
do a joined delete to your main table (thus, only one query).
EXPLAIN DELETE is your friend.
Hi all, > > COPY FROM a file with all the ID's to delete, into a temporary table, and do a joined delete to your main table (thus, only one query). I already did this, but I don't have idea about how to do this join, could you give me a hint ;-) ? Thank you very much -- Arnau
Arnau wrote:
> Hi all,
>
> >
> > COPY FROM a file with all the ID's to delete, into a temporary
> table, and do a joined delete to your main table (thus, only one query).
>
>
> I already did this, but I don't have idea about how to do this join,
> could you give me a hint ;-) ?
>
> Thank you very much
maybe something like this:
DELETE FROM statistics_sasme s
LEFT JOIN temp_table t ON (s.statistic_id = t.statistic_id)
WHERE t.statistic_id IS NOT NULL
Kevin wrote:
> Arnau wrote:
>
>> Hi all,
>>
>> >
>> > COPY FROM a file with all the ID's to delete, into a temporary
>> table, and do a joined delete to your main table (thus, only one query).
>>
>>
>> I already did this, but I don't have idea about how to do this join,
>> could you give me a hint ;-) ?
>>
>> Thank you very much
>
>
> maybe something like this:
>
> DELETE FROM statistics_sasme s
> LEFT JOIN temp_table t ON (s.statistic_id = t.statistic_id)
> WHERE t.statistic_id IS NOT NULL
>
Why can't you do:
DELETE FROM statistics_sasme s JOIN temp_table t ON (s.statistic_id =
t.statistic_id);
Or possibly:
DELETE FROM statistics_sasme s
WHERE s.id IN (SELECT t.statistic_id FROM temp_table t);
I'm not sure how delete exactly works with joins, but the IN form should
be approximately correct.
John
=:->