SELECT DISTINCT never uses an index?
От | Bill Moran |
---|---|
Тема | SELECT DISTINCT never uses an index? |
Дата | |
Msg-id | 20160707165619.037e6730c53316c263343895@potentialtech.com обсуждение исходный текст |
Ответы |
Re: SELECT DISTINCT never uses an index?
|
Список | 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
В списке pgsql-hackers по дате отправления: