X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C8D58D1C511; Tue, 22 Jul 2003 18:35:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 84391-01; Tue, 22 Jul 2003 15:34:56 -0300 (ADT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 44E30D1C50A; Tue, 22 Jul 2003 15:35:39 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id h6MIdP025166; Tue, 22 Jul 2003 20:39:25 +0200 Received: from GMENDOLA2 (gmendola2.mm.eutelsat.org [194.214.173.26]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with SMTP id h6MIWpR15557; Tue, 22 Jul 2003 20:32:51 +0200 Message-ID: <010601c35080$10256cf0$152aa8c0@GMENDOLA2> From: "Mendola Gaetano" To: , Cc: References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2> <200307221056.14913.josh@agliodbs.com> Subject: Re: [PERFORM] Wrong plan or what ? Date: Tue, 22 Jul 2003 20:35:45 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/216 X-Sequence-Number: 9556 "Josh Berkus" > Gaetano, > > > QUERY PLAN > > Hash Join (cost=265.64..32000.76 rows=40612 width=263) (actual > > time=11074.21..11134.28 rows=10 loops=1) > > Hash Cond: ("outer".id_user = "inner".id_user) > > -> Seq Scan on user_logs ul (cost=0.00..24932.65 rows=1258965 width=48) > > (actual time=0.02..8530.21 rows=1258966 loops=1) > > OK, here's your problem > > The planner thinks that you're going to get 40162 rows out of the final join, > not 10. If the row estimate was correct, then the Seq Scan would be a > reasonable plan. But it's not. Here's some steps you can take to clear > 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 > 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 ANALYZE) > > Then test again! No improvement at all, I pushed default_statistics_target to 1000 but the rows expected are still 40612 :-( Of course I restarted the postmaster and I vacuumed analyze the DB Thank you Gaetano