Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wao00-0003mk-JH for pgsql-performance@arkaria.postgresql.org; Thu, 17 Apr 2014 15:13:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wanzz-0003nr-Im for pgsql-performance@arkaria.postgresql.org; Thu, 17 Apr 2014 15:13:11 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WanyW-0001rQ-Bs for pgsql-performance@postgresql.org; Thu, 17 Apr 2014 15:11:40 +0000 Received: from smtp05.smtpout.orange.fr ([80.12.242.127] helo=smtp.smtpout.orange.fr) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WanyS-0006hL-Vg for pgsql-performance@postgresql.org; Thu, 17 Apr 2014 15:11:39 +0000 Received: from axege.com ([82.127.232.162]) by mwinf5d28 with ME id r3Ba1n00K3WtTCJ033Bao3; Thu, 17 Apr 2014 17:11:34 +0200 X-ME-Helo: axege.com X-ME-Date: Thu, 17 Apr 2014 17:11:34 +0200 X-ME-IP: 82.127.232.162 Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id 3331B500F8 for ; Thu, 17 Apr 2014 17:11:34 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id y9GG8NtVgvhi for ; Thu, 17 Apr 2014 17:11:25 +0200 (CEST) Received: from [10.0.0.75] (unknown [10.0.0.75]) (using TLSv1 with cipher DHE-RSA-AES128-SHA (128/128 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 9697D4FF10 for ; Thu, 17 Apr 2014 17:11:25 +0200 (CEST) Message-ID: <534FEF1D.5010706@axege.com> Date: Thu, 17 Apr 2014 17:11:25 +0200 From: Franck Routier User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: Fast distinct not working as expected Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 1.6 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Hi, we are using a mono-column index on a huge table to try to make a quick 'select distinct ' on the column. This used to work fine, but... it does not anymore. We don't know what happened. Here are the facts: - request: SELECT dwhinv___rfovsnide::varchar FROM dwhinv WHERE dwhinv___rfovsnide > '201212_cloture' ORDER BY dwhinv___rfovsnide LIMIT 1 - Plan : Limit (cost=0.00..1.13 rows=1 width=12) (actual time=5798.915..5798.916 rows=1 loops=1) -> Index Scan using vsn_idx on dwhinv (cost=0.00..302591122.05 rows=267473826 width=12) (actual time=5798.912..5798.912 rows=1 loops=1) Index Cond: ((dwhinv___rfovsnide)::text > '201212_cloture'::text) Total runtime: 5799.141 ms - default_statistics_target = 200; - postgresql Version 8.4 - Index used : CREATE INDEX vsn_idx ON dwhinv USING btree (dwhinv___rfovsnide); There are 26 distinct values of the column. This query used to take some milliseconds at most. The index has been freshly recreated. What could be the problem ? Franck -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance