| From: | Jarrod Chesney <jarrod(dot)chesney(at)gmail(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Delete performance |
| Date: | 2011-05-31 00:08:28 |
| Message-ID: | 6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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 happens almost 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 to the "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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jenish | 2011-05-31 07:20:59 | Strange behavior of child table. |
| Previous Message | Greg Smith | 2011-05-28 21:47:12 | Re: The shared buffers challenge |