Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A04879FB33D for ; Mon, 21 May 2007 15:32:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34105-09 for ; Mon, 21 May 2007 15:31:52 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from k2smtpout01-01.prod.mesa1.secureserver.net (k2smtpout01-02.prod.mesa1.secureserver.net [64.202.189.89]) by postgresql.org (Postfix) with SMTP id 850579FB28E for ; Mon, 21 May 2007 15:31:50 -0300 (ADT) Received: (qmail 15071 invoked from network); 21 May 2007 18:17:59 -0000 Received: from unknown (HELO mail.servantwebhosting.com) (68.178.159.82) by k2smtpout01-01.prod.mesa1.secureserver.net (64.202.189.88) with ESMTP; 21 May 2007 18:17:59 -0000 Received: (qmail 11404 invoked by uid 2527); 21 May 2007 11:17:59 -0700 Received: from 70.64.210.50 by jonah.prod.phx1.secureserver.net (envelope-from , uid 2525) with qmail-scanner-1.25-st-qms (clamdscan: 0.86.2/989. spamassassin: 3.1.8. perlscan: 1.25-st-qms. Clear:RC:0(70.64.210.50):SA:0(1.4/5.0):. Processed in 1.713657 secs); 21 May 2007 18:17:59 -0000 X-Antivirus-MYDOMAIN-Mail-From: pgsql-performance@nullmx.com via jonah.prod.phx1.secureserver.net X-Antivirus-MYDOMAIN: 1.25-st-qms (Clear:RC:0(70.64.210.50):SA:0(1.4/5.0):. Processed in 1.713657 secs Process 11378) X-Envelope-From: pgsql-performance@nullmx.com Received: from s010600e0296eec95.mj.shawcable.net (HELO shach) (rob@nullmx.com@70.64.210.50) by mail.servantwebhosting.com with SMTP; 21 May 2007 11:17:57 -0700 From: "Chuck D." Reply-To: pgsql-performance@nullmx.com To: pgsql-performance@postgresql.org Subject: Re: Rewriting DISTINCT and losing performance Date: Mon, 21 May 2007 12:17:44 -0600 User-Agent: KMail/1.9.5 References: <200705202228.32129.pgsql-performance@nullmx.com> <200705210909.44335.pgsql-performance@nullmx.com> <4651D813.5050100@archonet.com> In-Reply-To: <4651D813.5050100@archonet.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200705211217.45205.pgsql-performance@nullmx.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.043 tagged_above=0 required=5 tests=AWL=-0.093, BAYES_50=0.001, FORGED_RCVD_HELO=0.135 X-Spam-Level: X-Archive-Number: 200705/396 X-Sequence-Number: 24786 On Monday 21 May 2007 11:34, Richard Huxton wrote: > Chuck D. wrote: > > The only thing I can think of is that the CLUSTERing on city.country_id > makes the system think it'll be cheaper to seq-scan the whole table. > > I take it you have got 2 million rows in "city"? Well here is where it gets strange. The CLUSTER was just one thing I tried to do to enhance the performance. I had the same result prior to cluster. However, after updating that country_id column to NOT NULL and eliminating NULL values it will use the country_id index and perform quickly. Oddly enough, the original table, world_city still has NULL values in the country_id column and it has always used the country_id index. Doesn't that seem a bit strange? Does it have to do with the smaller size of the new table maybe?