Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AFFE74771CD for ; Wed, 15 Jan 2003 15:17:53 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 5045D476624 for ; Wed, 15 Jan 2003 14:30:29 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2315106; Wed, 15 Jan 2003 11:30:32 -0800 Content-Type: text/plain; charset="utf-8" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Roman Fail" , Subject: Re: 7.3.1 New install, large queries are slow Date: Tue, 14 Jan 2003 11:32:32 -0800 X-Mailer: KMail [version 1.4] References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BF9@pos_pdc.posportal.com> In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4BF9@pos_pdc.posportal.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200301141132.32277.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/120 X-Sequence-Number: 767 Roman, First, if this is a dedicated PostgreSQL server, you should try increasing= =20 your shared_buffers to at least 512mb (65536) if not 1GB (double that) and= =20 adjust your shmmax and shmmall to match. Second, you will probably want to increase your sort_mem as well. How much= =20 depeneds on the number of concurrent queries you expect to be running and= =20 their relative complexity. Give me that information, and I'll offer you= =20 some suggestions. Part of your slow query=20 Your query problem is hopefully relatively easy. The following clause is 9= 5%=20 of your query time: > -> Index Scan using=20 batchdetail_ix_tranamount_idx on batchdetail d (cost=3D0.00..176768.18=20 rows=3D44010 width=3D293) (actual time=3D35.48..1104625.54 rows=3D370307 lo= ops=3D1)=20 >=20 See the actual time figures? This one clause is taking 1,104,590 msec!=20= =20=20 Now, why? Well, look at the cost estimate figures in contrast to the actual row count: estimate rows =3D 44,010 real rows 370,307 That's off by a factor of 9. This index scan is obviously very cumbersome= =20 and is slowing the query down. Probably it should be using a seq scan=20 instead ... my guess is, you haven't run ANALYZE in a while and the incorre= ct=20 row estimate is causing the parser to choose a very slow index scan. Try running ANALYZE on your database and re-running the query. Also try= =20 using REINDEX on batchdetail_ix_tranamount_idx . Second, this clause near the bottom: -> Seq Scan on purc1 p1 (cost=3D0.00..442= 59.70=20 rows=3D938770 width=3D19) (actual time=3D98.09..4187.32 rows=3D938770 loops= =3D5)=20 ... suggests that you could save an additional 4 seconds by figuring out a = way=20 for the criteria on purc1 to use a relevant index -- but only after you've= =20 solved the problem with batchdetail_ix_tranamount_idx. Finally, if you really want help, post the query. --=20 -Josh Berkus Aglio Database Solutions San Francisco