From: Josh Berkus <josh@agliodbs.com>
To: tsarevich@gmail.com
Cc: Postgres - Performance <pgsql-performance@postgresql.org>
Subject: Re: Tuning, configuration for 7.3.5 on a Sun E4500
Date: Tue, 8 Mar 2005 09:10:57 -0800
Message-ID: <200503080910.57409.josh@agliodbs.com> (raw)
In-Reply-To: <156a90fe05030723477c3ee7d3@mail.gmail.com>
References: <156a90fe050307064678d5a6e9@mail.gmail.com>
<200503070931.06241.josh@agliodbs.com>
<156a90fe05030723477c3ee7d3@mail.gmail.com>
Tsarevich,
> Analyze has been run on the database quite frequently during the
> course of us trying to figure out this performance issue. It is also
> a task that is crontabbed nightly.
Hmmm. Then you probably need to up the STATISTICS levels on the target
column, because PG is mis-estimating the number of rows returned
significantly. That's done by:
ALTER TABLE {table} ALTER COLUMN {column} SET STATISTICS {number}
Generally, I find that if mis-estimation occurs, you need to raise statistics
to at least 250.
Here's where I see the estimation issues with your EXPLAIN:
-> Index Scan
using component_commercial_order_id_ix on component (cost=0.00..3.85
rows=1 width=28) (actual time=0.17..0.18 rows=1 loops=46376)
Index Cond:
(component.commercial_order_id = "outer".commercial_order_id)
Filter:
((raised_dtm >= '2003-01-01 00:00:00'::timestamp without time zone)
AND (raised_dtm <= '2005-01-01 23:59:59'::timestamp without time zone)
AND ((component_type_id = 3) OR (component_type_id = 2) OR
(component_type_id = 1)))
-> Index Scan using communication_component_id_ix on
communication (cost=0.00..20.90 rows=16 width=8) (actual
time=0.12..0.14 rows=1 loops=34638)
Index Cond: (component_id = $0)
So it looks like you need to raise the stats on communication.component_id and
component.commercial_order_id,raised_dtm,component_type_id. You also may
want to consider a multi-column index on the last set.
BTW, if you have any kind of data update traffic at all, ANALYZE once a day is
not adequate.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, tsarevich@gmail.com
Subject: Re: Tuning, configuration for 7.3.5 on a Sun E4500
In-Reply-To: <200503080910.57409.josh@agliodbs.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