X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D43A8564B4 for ; Tue, 8 Mar 2005 17:11:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34780-07 for ; Tue, 8 Mar 2005 17:11:17 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 7389F564A2 for ; Tue, 8 Mar 2005 17:11:12 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7105556; Tue, 08 Mar 2005 09:13:01 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: Subject: Re: Tuning, configuration for 7.3.5 on a Sun E4500 Date: Tue, 8 Mar 2005 09:10:57 -0800 User-Agent: KMail/1.6.2 Cc: Postgres - Performance References: <156a90fe050307064678d5a6e9@mail.gmail.com> <200503070931.06241.josh@agliodbs.com> <156a90fe05030723477c3ee7d3@mail.gmail.com> In-Reply-To: <156a90fe05030723477c3ee7d3@mail.gmail.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200503080910.57409.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/130 X-Sequence-Number: 10835 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=20 column, because PG is mis-estimating the number of rows returned=20 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 statisti= cs=20 to at least 250. Here's where I see the estimation issues with your EXPLAIN: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using component_commercial_order_id_ix on component =A0(cost=3D0.00..3.85 rows=3D1 width=3D28) (actual time=3D0.17..0.18 rows=3D1 loops=3D46376) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (component.commercial_order_id =3D "outer".commercial_order_id) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((raised_dtm >=3D '2003-01-01 00:00:00'::timestamp without time zone) AND (raised_dtm <=3D '2005-01-01 23:59:59'::timestamp without time zone) AND ((component_type_id =3D 3) OR (component_type_id =3D 2) OR (component_type_id =3D 1))) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using communication_com= ponent_id_ix on communication =A0(cost=3D0.00..20.90 rows=3D16 width=3D8) (actual time=3D0.12..0.14 rows=3D1 loops=3D34638) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (component_id = =3D $0) So it looks like you need to raise the stats on communication.component_id = and=20 component.commercial_order_id,raised_dtm,component_type_id. You also may= =20 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=20 not adequate. =2D-=20 Josh Berkus Aglio Database Solutions San Francisco