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 DD9643A442C for ; Thu, 25 Nov 2004 01:18:29 +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 48689-08 for ; Thu, 25 Nov 2004 01:18:22 +0000 (GMT) Received: from trofast.sesse.net (trofast.sesse.net [129.241.93.32]) by svr1.postgresql.org (Postfix) with ESMTP id 52D3E3A1D8D for ; Thu, 25 Nov 2004 01:18:24 +0000 (GMT) Received: from sesse by trofast.sesse.net with local (Exim 3.36 #1 (Debian)) id 1CX8H9-0001DE-00 for ; Thu, 25 Nov 2004 02:18:23 +0100 Date: Thu, 25 Nov 2004 02:18:23 +0100 From: "Steinar H. Gunderson" To: pgsql-performance@postgresql.org Subject: Re: Postgres vs. MySQL Message-ID: <20041125011823.GA4640@uio.no> Mail-Followup-To: pgsql-performance@postgresql.org References: <1101302058.338.74.camel@haddock.cmima.csic.es> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1101302058.338.74.camel@haddock.cmima.csic.es> X-Operating-System: Linux 2.6.8.1 on a i686 User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/389 X-Sequence-Number: 9343 On Wed, Nov 24, 2004 at 02:14:18PM +0100, Evilio del Rio wrote: > I have installed the dspam filter > (http://www.nuclearelephant.com/projects/dspam) on our mail server > (RedHat 7.3 Linux with sendmail 8.13 and procmail). I have ~300 users > with a quite low traffic of 4000 messages/day. So it's a quite common > platform/environment, nothing spectacular. We just had a case just like this on #postgresql. The (somewhat surprising) solution was increasing the statistics target on the "token" column to something like 200, which makes the planner choose an index scan instead of a sequential scan. For the people who did not follow the case: The culprit is a query like SELECT * FROM table WHERE token IN ('346369873476346', '4376376034', ...) (token is a numeric(20,0)) With one entry in the IN (), the cost of an index scan was estimated to 4.77; with ten entries, it was about 48, but with 574 entries the estimated cost was 513565 (!!), making the planner prefer an index scan to 574 consecutive index scans. Upping the statistics target made the planner estimate the cost to about ~4000, and thus select the index scan, which was two orders of magnitude faster. BTW, this case was with PostgreSQL 7.4.6, not 7.3 as the poster here is reporting. /* Steinar */ -- Homepage: http://www.sesse.net/