Re: delete seems to be getting blocked

however if the for every occuerence of the foreign key there are some 2000 rows in the table, is it ok to still have an index on that foreign key.

also will index scan still take place or postgres will itself choose to do sequential scan.

however, i have also noticed that even though the indexes exixt, still the sequential can takes place.
how can this be avoided, will i have to set the enable_seq_scan to off?

thanks,
regards
surabhi

________________________________

From: pgsql-general-owner(at)postgresql(dot)org on behalf of Michael Fuhr
Sent: Mon 6/12/2006 6:18 PM
To: surabhi.ahuja
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] delete seems to be getting blocked

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

On Mon, Jun 12, 2006 at 12:54:15PM +0530, surabhi.ahuja wrote:
> i have four tables in my database
> TAB1, has one primary key T1
>
> TAB2 , has 2 fields, one is the primary ley T2 and the other one
> is the foreign key T1(from TAB1)
>
> TAB3 also has 2 fields, one is the primary ley T3 and the other
> is the foreign key T2(from TAB2)
>
> TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3)

Do the foreign key columns in TAB2, TAB3, and TAB4 have indexes?
How many rows do the tables have?

> the disk is 100% full.
>
> i open psql <datbase_name> and do
> delete from TAB1
>
> nothing seems to be happening for a long time, although when i do
> top, it shows postgres taking 99%.

For each record you delete in TAB1 the database must search TAB2
to check for referential integrity violations or cascading operations
(ON DELETE CASCADE, ON DELETE SET NULL, etc.). If the foreign key
column in TAB2 doesn't have an index then each row deleted from
TAB1 will result in a sequential scan on TAB2; likewise with TAB3
if you modify TAB2 and with TAB4 if you modify TAB3.

If the tables are large then make sure you have indexes on the
foreign key columns. If you create indexes then you might need to
start a new session due to plan caching.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org