Re: Tuning, configuration for 7.3.5 on a Sun E4500 - Mailing list pgsql-performance

From Josh Berkus
Subject Re: Tuning, configuration for 7.3.5 on a Sun E4500
Date
Msg-id 200503080910.57409.josh@agliodbs.com
Whole thread Raw
In response to Re: Tuning, configuration for 7.3.5 on a Sun E4500  (<tsarevich@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: John A Meinel
Date:
Subject: Re: pl/pgsql faster than raw SQL?
Next
From: Gaetano Mendola
Date:
Subject: Re: bad plan