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?
