Re: Performance woes

["Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx>]

> 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??!?!? 
>
>   

Well from a postgres perspective you are still only using one CPU for 
this query. Are you running 32 bit or 64bit?
What is the explain analyze? What is your statistics value?  What 
version of PostgreSQL? What is your sort_mem or work_mem?

Joshua D. Drake



> 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? 
>
>
>
>