Message-ID: <48E3E8DF.3040404@rochgrp.com>
Date: Wed, 01 Oct 2008 17:17:19 -0400
From: "H. William Connors II" <bconnors@rochgrp.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20070102)
MIME-Version: 1.0
To: lennin.caro@yahoo.com
CC: pgsql-performance@postgresql.org
Subject: Re: bizarre query performance question
References: <706433.11623.qm@web59512.mail.ac4.yahoo.com>
In-Reply-To: <706433.11623.qm@web59512.mail.ac4.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Lennin Caro wrote:
>
> --- On Wed, 10/1/08, H. William Connors II <bconnors@rochgrp.com> wrote:
>
>   
>> From: H. William Connors II <bconnors@rochgrp.com>
>> Subject: [PERFORM] bizarre query performance question
>> To: pgsql-performance@postgresql.org
>> Date: Wednesday, October 1, 2008, 8:34 PM
>> I have two fairly simple tables as described below.  The
>> relationship 
>> between them is through assignment_id.  The problem is when
>> I try to 
>> join these two tables the planner does a sequential scan on
>>
>> fa_assignment_detail and the query takes forever to
>> resolve.  I've run 
>> the usual vacuum and analyze commands with no changes. 
>> I'm not sure how 
>> long the query actually takes to resolve as its been
>> running for over 30 
>> minutes now (FYI this is on a 8 core IBM Power5 550 with 8
>> GB of RAM) 
>> running RedHat Enterprise 9 and postgresql 8.3.3.  Any
>> thoughts?
>>
>> \d fa_assignment
>>                       Table
>> "public.fa_assignment"
>>      Column      |            Type             |      
>> Modifiers
>> -----------------+-----------------------------+------------------------
>>  scenario_id     | integer                     | not null
>>  prospect_id     | integer                     | not null
>>  assignment_id   | integer                     | not null
>>  valid           | boolean                     | not null
>> default false
>>  modified        | boolean                     | not null
>> default true
>>  modify_ts       | timestamp without time zone |
>>  modify_username | character varying(32)       |
>> Indexes:
>>     "pk_fa_assignment" PRIMARY KEY, btree
>> (scenario_id, prospect_id)
>>     "fa_assignment_idx1" btree (assignment_id)
>> CLUSTER
>>     "fa_assignment_idx2" btree (scenario_id,
>> assignment_id)
>>     "fa_assignment_idx3" btree (prospect_id)
>> Foreign-key constraints:
>>     "fk_fa_prospect" FOREIGN KEY (prospect_id)
>> REFERENCES 
>> fa_prospect(prospect_id) DEFERRABLE
>>     "fk_fa_scenario" FOREIGN KEY (scenario_id)
>> REFERENCES 
>> fa_scenario(scenario_id) DEFERRABLE
>>
>>
>>
>> \d fa_assignment_detail
>>                   Table
>> "public.fa_assignment_detail"
>>      Column      |            Type             |      
>> Modifiers
>> -----------------+-----------------------------+------------------------
>>  assignment_id   | integer                     | not null
>>  type            | character varying(8)        | not null
>>  resource_id     | integer                     |
>>  create_ts       | timestamp without time zone | not null
>>  create_username | character varying(32)       | not null
>>  modify_ts       | timestamp without time zone |
>>  modify_username | character varying(32)       |
>>  locked          | boolean                     | not null
>> default false
>>  locked_ts       | timestamp without time zone |
>>  locked_username | character varying(32)       |
>> Indexes:
>>     "pk_fa_assignment_detail" PRIMARY KEY, btree
>> (assignment_id, type)
>>     "fa_assignment_detail_idx1" btree
>> (resource_id)
>>     "fa_assignment_detail_idx2" btree
>> (assignment_id)
>> Foreign-key constraints:
>>     "fk_fa_resource1" FOREIGN KEY (resource_id)
>> REFERENCES 
>> fa_resource(resource_id) DEFERRABLE
>>
>>
>>
>> fa_assignment has 44184945 records
>> fa_assignment_detail has 82196027 records
>>
>>
>>
>> explain select * from fa_assignment fa JOIN
>> fa_assignment_detail fad ON 
>> (fad.assignment_id = fa.assignment_id) where fa.scenario_id
>> = 0;
>>
>>                                               QUERY 
>> PLAN                      
>> -------------------------------------------------------------------------------------------------------
>>  Hash Join  (cost=581289.72..4940729.76 rows=9283104
>> width=91)
>>    Hash Cond: (fad.assignment_id = fa.assignment_id)
>>    ->  Seq Scan on fa_assignment_detail fad 
>> (cost=0.00..1748663.60 
>> rows=82151360 width=61)
>>    ->  Hash  (cost=484697.74..484697.74 rows=4995439
>> width=30)
>>          ->  Bitmap Heap Scan on fa_assignment fa  
>> (cost=93483.75..484697.74 rows=4995439 width=30)
>>                Recheck Cond: (scenario_id = 0)
>>                ->  Bitmap Index Scan on
>> fa_assignment_idx2  
>> (cost=0.00..92234.89 rows=4995439 width=0)
>>                      Index Cond: (scenario_id = 0)
>> (8 rows)
>>
>>
>>     
>
> The Fk for the table fa_assignment_detail to fa_assignment is nor relationate whit the column assignment_id
>
>
>       
>
>
>   
That is because assignment_id is because there can be many records in 
fa_assignment that use the same assignment_id and thus it isn't unique 
there.  I can join other tables not related through a foreign key using 
an index so I'm unclear why this situation is different.