Re: slow DELETE on 12 M row table

[Janet Jacobsen <jsjacobsen@xxxxxxx>]

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@xxxxxxx> 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.