Re: slow DELETE on 12 M row table

Thank you for the answers. Very helpful.

Between the time that I sent my original post and saw your reply,
I tried to drop a couple of foreign key constraints. The alter
table statements also showed up as "waiting" when I ran ps aux.

I took your suggestion to run pg_locks and pg_stat_activity.
pg_stat_activity showed that I had three statements that were
waiting, and that there was one user whose query was given
as "<insufficient privilege>". I killed the process associated
with that user, and my three waiting statements executed
immediately.

I assume that killing the user's process released the lock on the
table. This user has only SELECT privileges. Under what
conditions would a SELECT lock a table. The user connects
to the database via a (Python?) script that runs on another
machine. Would this way of connecting to the database result
in a lock?

Thanks,
Janet

On 25/06/2009 08:17 p.m., Greg Stark wrote:
> On Fri, Jun 26, 2009 at 3:33 AM, Janet Jacobsen<jsjacobsen(at)lbl(dot)gov> wrote:
>
>> (1) is my interpretation of the posts correct, i.e., if I am deleting
>> rows from
>> table1, where the pkey of table 1 is a fkey in table 2, then do I need
>> to create an
>> index on the fkey field in table 2?
>>
>
> Exactly right. The index on the table2 is optional but deletes and
> updates on table1 will be very slow without it as it has to do a full
> table scan of table2 to ensure no references remain.
>
>
>> (2) do you have any suggestions on how I can determine why it is taking
>> several hours to create an index on a field in a table with 12 M rows? does
>> that seem like a reasonable amount of time? I have maintenance_work_mem
>> set to 512MB - is that too low, or is that the wrong config parameter to
>> change?
>> [ps aux shows "CREATE INDEX waiting"; there is nothing (no image processing)
>> running on the machine at this time]
>>
>
> 512MB is a perfectly reasonable maintenance_work_mem. Larger than that
> is overkill.
>
> "waiting" means it's blocked trying to acquire a lock. Some open
> transaction has the table you're trying to index locked. Look in
> pg_locks and pg_stat_activity to find out who.
>
>
>
>> (3) would I be better off dropping the foreign keys? in general, is it
>> workable to
>> have foreign keys on tables with > 100 M rows (assuming I create all of
>> the 'right'
>> indexes)?
>>
>
> If you have the right indexes then the size of the table shouldn't be
> a large factor. The number of transactions per second being processed
> are perhaps more of a factor but even on very busy systems, most of
> the time foreign key constraints aren't a problem to keep.
>
>