From: Lennin Caro <lennin.caro@yahoo.com>
To: pgsql-performance@postgresql.org, "H. William Connors II" <bconnors@rochgrp.com>
Subject: Re: bizarre query performance question
Date: Wed, 1 Oct 2008 14:18:08 -0700 (PDT)
Message-ID: <706433.11623.qm@web59512.mail.ac4.yahoo.com> (raw)
In-Reply-To: <48E3DEE9.5000002@rochgrp.com>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lennin.caro@yahoo.com, bconnors@rochgrp.com
Subject: Re: bizarre query performance question
In-Reply-To: <706433.11623.qm@web59512.mail.ac4.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox