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
