SELECT DISTINCT never uses an index?

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: SELECT DISTINCT never uses an index?
Date: 2016-07-07 20:56:19
Message-ID: 20160707165619.037e6730c53316c263343895@potentialtech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Take the following table as an example:

CREATE TABLE grue (
id SERIAL PRIMARY KEY,
size VARCHAR(255)
);
CREATE INDEX grue_size ON grue(size);

Now insert approximately eleventy jillion rows, but ensure
that there are only about 20 distinct values for size.

SELECT DISTINCT size FROM grue;

Always does a seq scan on Postgres 9.5.2. (Yes, I know we're
a patch behind, the upgrade is on the schedule) on
Ubuntu 14.

I would expect it to be possible, and significantly more
efficient to do an index scan for that query. Is this
a bug, an optimization that is simply waiting for someone
to take the time to implement, or is there some underlying
reason why this isn't possible that I'm not seeing.

And, yes, I know that's not a normalized table and that
properly normalizing it makes the problem disappear. And
yes, this is repeatable (I'm working with about 6 tables
with similar structure that all exhibit the same
behavior) and yes I've done ANALYZE and VACUUM and the
behavior doesn't change.

--
Bill Moran

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-07-07 21:49:27 Re: SELECT DISTINCT never uses an index?
Previous Message Robert Haas 2016-07-07 20:28:07 Re: strange explain in upstream - subplan 1 twice - is it bug?