X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 60F56D1C4BF for ; Tue, 22 Jul 2003 17:57:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 88888-08 for ; Tue, 22 Jul 2003 17:57:30 +0000 (GMT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by svr1.postgresql.org (Postfix) with ESMTP id B8011D1C4C8 for ; Tue, 22 Jul 2003 14:57:29 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3244654; Tue, 22 Jul 2003 10:57:32 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Mendola Gaetano" , Subject: Re: Wrong plan or what ? Date: Tue, 22 Jul 2003 10:56:14 -0700 User-Agent: KMail/1.4.3 References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2> In-Reply-To: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200307221056.14913.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/373 X-Sequence-Number: 2666 Gaetano, > QUERY PLAN > Hash Join (cost=3D265.64..32000.76 rows=3D40612 width=3D263) (actual > time=3D11074.21..11134.28 rows=3D10 loops=3D1) > Hash Cond: ("outer".id_user =3D "inner".id_user) > -> Seq Scan on user_logs ul (cost=3D0.00..24932.65 rows=3D1258965 wi= dth=3D48) > (actual time=3D0.02..8530.21 rows=3D1258966 loops=3D1) OK, here's your problem The planner thinks that you're going to get 40162 rows out of the final joi= n,=20 not 10. If the row estimate was correct, then the Seq Scan would be a=20 reasonable plan. But it's not. Here's some steps you can take to clear= =20 things up for the planner: 1) Make sure you've VACUUM ANALYZED 2) Adjust the following postgresql.conf statistics: a) effective_cache_size: increase to 70% of available (not used by other= =20 processes) RAM. b) random_page_cost: decrease, maybe to 2. c) default_statistics_target: try increasing to 100 (warning: this will significantly increase the time required to do ANALYZ= E) Then test again! --=20 -Josh Berkus Aglio Database Solutions San Francisco