Skip site navigation (1) Skip section navigation (2)

Re: slow DELETE on 12 M row table

From: Janet Jacobsen <jsjacobsen(at)lbl(dot)gov>
To: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: slow DELETE on 12 M row table
Date: 2009-06-26 07:34:07
Message-ID: 4A4479EF.2030707@lbl.gov (view raw or flat)
Thread:
Lists: pgsql-performance
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.
>
>   

In response to

Responses

pgsql-performance by date

Next:From: Marcin StępnickiDate: 2009-06-26 10:40:40
Subject: Re: slow DELETE on 12 M row table
Previous:From: Richard HuxtonDate: 2009-06-26 05:47:41
Subject: Re: slow DELETE on 12 M row table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group