Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 52CA71C7037B for ; Tue, 13 Dec 2011 15:58:15 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RaYUN-0001pr-MQ for pgsql-performance@postgresql.org; Tue, 13 Dec 2011 19:58:15 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pBDJvvGs009563; Tue, 13 Dec 2011 14:57:57 -0500 (EST) To: Jon Nelson cc: pgsql-performance@postgresql.org Subject: Re: select distinct uses index scan vs full table scan In-reply-to: References: Comments: In-reply-to Jon Nelson message dated "Tue, 13 Dec 2011 12:12:47 -0600" Date: Tue, 13 Dec 2011 14:57:57 -0500 Message-ID: <9562.1323806277@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201112/103 X-Sequence-Number: 45902 Jon Nelson writes: > I've got a 5GB table with about 12 million rows. > Recently, I had to select the distinct values from just one column. > The planner chose an index scan. The query took almost an hour. > When I forced index scan off, the query took 90 seconds (full table scan). Usually, we hear complaints about the opposite. Are you using nondefault cost settings? > The planner estimated 70,000 unique values when, in fact, there are 12 > million (the value for this row is *almost* but not quite unique). > What's more, despite bumping the statistics on that column up to 1000 > and re-analyzing, the planner now thinks that there are 300,000 unique > values. Accurate ndistinct estimates are hard, but that wouldn't have much of anything to do with this particular choice, AFAICS. > How can I tell the planner that a given column is much more unique > than, apparently, it thinks it is? 9.0 and up have ALTER TABLE ... ALTER COLUMN ... SET n_distinct. regards, tom lane