Search Postgresql Archives

Re: delete is extremely slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Scott Marlowe wrote:

On Fri, 2006-05-26 at 09:51, Emi Lu wrote:
Emi Lu wrote:

Tried to run:

delete from tableA where id <600000;

It is extremely slow.

id is integer and also the primary key. tableA has around 90000 records.

(I do not want to run truncate since I have some foreign key constraint
setup based on tableA. Besides not all data I want to delete; only part
of them).

Since you have some FK constrains on A, so delete each line of A will
confirm that it is not depended, which involves a lookup on the dependent
table. Do you have indexes built on these dependents?

No, I do not have indexes setup for those dependents.

If I setup index for those columns, it will speed up the deletion, right?

Thank you.


http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php

If deletioin is 80% data, will index helps?

Yep.  If your master table has 10,000 rows, and the dependent table has
100,000 rows, and you're deleting half of the master table (5,000 rows)
then you'll have 5,000 sequential scans of the 100,000 row dependent
table.
What about the reverse, 90,000 in the master table , while 500 records in each dependant tables?




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux