Ben, > A couple of questions about the ADD CONSTRAINT. The foreign key column > on the local table is indexed, and there are only ~50 unique values, > so the db *could* come up with the unique values pretty quickly and > then check them. This would indeed be a nice optimization, especially now that we have index-only scans; you could do a VACUUM FREEZE on the tables and then add the constraint. > b) Delete old rows from the table so it's not so big. Feels a bit > hacky just to fix this issue. > > c) Get rid of this foreign key constraint entirely and just check it > in code when we insert. Pragmatic solution, but not ideal. d) add a trigger instead of an actual FK. Slower to execute on subsequent updates/inserts, but doesn't need to be checked. e) do something (slony, scripts, whatever) so that you're incrementally updating this table instead of recreating it from scratch each time. > * We're running "PostgreSQL 9.0.2, compiled by Visual C++ build 1500, > 64-bit" on 64-bit Windows Server 2008 SP1 (6.0.6001) I will point out that you are missing a whole ton of bug fixes, including two critical security patches. > * The "ratesrequests" table has two text columns, one of which often > contains a few hundred to a couple of KB of data in the field. It is > added to rapidly. We regularly VACCUM ANALYZE it. > * As expected, the ADD CONSTRAINT has gotten slower over time as this > table grew. However -- I'm not 100% sure of this, but it seems to have > jumped recently (from 3-4 minutes to 7 minutes). > * http://www.postgresql.org/message-id/20030323112241.W14634-100000@xxxxxxxxxxxxxxxxxxxxxxx Probably the table just got larger than RAM. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
