Fast distinct not working as expected

From: Franck Routier <franck(dot)routier(at)axege(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Fast distinct not working as expected
Date: 2014-04-17 15:11:25
Message-ID: 534FEF1D.5010706@axege.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2014-04-17 15:57:48 Re: Fast distinct not working as expected
Previous Message amul sul 2014-04-17 07:05:43 Re: Workaround for working_mem max value in windows?