From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Fast distinct not working as expected
Date: Thu, 17 Apr 2014 17:11:25 +0200
Message-ID: <534FEF1D.5010706@axege.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: franck.routier@axege.com
Subject: Re: Fast distinct not working as expected
In-Reply-To: <534FEF1D.5010706@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox