I have a small company growing fast, selling a product based largely on Postgres. We have a rapidly growing database with (currently) 117 tables. Our primary server, an aging system based around an IDE, 2.4 Ghz P4, is being replaced by a new, 10k SCSI, dual-proc AMD opteron/64, with 4 GB of ECC RAM. At just about every task, the newer machine just blows away the P4. Rebuilding the database happens in < 1 minute, instead of nearly 1/2 hour! Copying GB of data files is blazing fast! But, the real money shot is a single query. It's big, nasty, and complex, and hit pretty hard. It's not IO bound, it is clearly CPU bound. I've allocated up to 3 GB of RAM for pg, and tweaked the shmmax and shared_buffers. And, it's exactly as fast on the new, dual-proc Opteron as the aging P4. 2.2 seconds. It's literally within 1 ms time! (2,206 ms vs 2,207 ms) Throwing more RAM at it makes no difference. WTF??!?!? A few questions: 1) Let's assume that I have some multipile foreign keys, and I join on three values. For example: Create table gangsters ( name varchar not null, birthdate integer not null, shirtnumber integer not null, primary key (name, birthdate, shirtnumber); create table children ( father_name varchar not null, father_bd integer not null, father_shirtnumber integer not null, birthdate integer not null, name varchar not null, foreign key (father_name, father_bd, father_shirtnumber) REFERENCES gangsters(name, birthdate, shirtnumber) ); We have two table declarations, each with implicit indexes: 1) table gangsters has a primary_key index on name, birthdate, shirtnumber. 2) children has an implicit index on father_name, father_bd, father_shirtnumber. (right?) If I were to join on gangster and children, EG: Select gangster.name AS father, gangster.birirthdate AS father_bd, children.name AS kid_name from gangster, children where gangster.name=children.father_name AND gangster.birthdate = children.father_bd AND gangster.shirtnumber=children.father_shirtnumber; Wouldn't this use the indexes implicitly created in the primary_key and foreign key constraints? -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
