Tsarevich,

> When running queries we are experiencing much bigger result times than
> anticipated.
>
> Attached is a copy of our postgresql.conf file and of our the table
> definitions and row counts.

Looks like you haven't run ANALYZE on the database anytime recently. Try that
and re-run.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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.


On Mon, 7 Mar 2005 09:31:06 -0800, Josh Berkus <josh@agliodbs.com> wrote:
> Tsarevich,
>
> > When running queries we are experiencing much bigger result times than
> > anticipated.
> >
> > Attached is a copy of our postgresql.conf file and of our the table
> > definitions and row counts.
>
> Looks like you haven't run ANALYZE on the database anytime recently. Try that
> and re-run.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings