Postgres Pro
Downloads
Home   >   mailing lists

Delete performance - Mailing list pgsql-performance

From Jarrod Chesney
Subject Delete performance
Date
Msg-id 6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com
Whole thread Raw
Responses Re: Delete performance  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Re: Delete performance  ("Pierre C" <lists@peufeu.com>)
Re: Delete performance  (Greg Smith <greg@2ndQuadrant.com>)
List pgsql-performance
Tree view 
Hi All
    My database uses joined table inheritance and my server version is 9.0

Version string    PostgreSQL 9.0rc1 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r1
p1.1,pie-0.4.5) 4.4.4, 64-bit     

I have about 120,000 records in the table that everything else inherits from, if i truncate-cascaded this table it
happensalmost instantly. If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it takes close to 10
minutes.

My foreign keys to the base table are all set with "ON DELETE CASCADE". I've looked though all the feilds that relate
tothe "ID" in the base table and created btree indexes for them. 

Can anyone outline what I need to verify/do to ensure i'm getting the best performance for my deletes?

Regards, Jarrod Chesney

pgsql-performance by date:

Previous
From: Greg Smith
Date:
Subject: Re: The shared buffers challenge
Next
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: Delete performance