From: H. William Connors II <[EMAIL PROTECTED]>
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)
