Skip site navigation (1) Skip section navigation (2)

bizarre query performance question

From: "H(dot) William Connors II" <bconnors(at)rochgrp(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: bizarre query performance question
Date: 2008-10-01 20:34:49
Message-ID: 48E3DEE9.5000002@rochgrp.com (view raw or flat)
Thread:
Lists: pgsql-performance
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)


Responses

pgsql-performance by date

Next:From: H. William Connors IIDate: 2008-10-01 21:17:19
Subject: Re: bizarre query performance question
Previous:From: Josh BerkusDate: 2008-10-01 20:12:59
Subject: Re: Confusing Query Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group