On Fri, Jun 26, 2009 at 3:33 AM, Janet Jacobsen<jsjacobsen@lbl.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.
--
greg
http://mit.edu/~gsstark/resume.pdf
