Hello list,
Following from a question I had yesterday, I'm wondering if there is
some way to summarize the unique values of an indexed column in
PostgreSQL without having the query scan the whole table. For my
current work, I have many large tables, all of which have an indexed
column for the year from which each row of data was recorded. This year
column contains a small number of unique values (e.g., where a large
table contains data from one, two or three years...so far). I've been
getting the unique values by executing queries like 'select distinct
year from [table];', or 'select year from [table] group by year;'.
It would seem to me (as I mis-understand it) that if the column is
indexed, that the whole table shouldn't need to be queried when all I
want to get these values. I figure I should somehow be able to benefit
from the index, which presumably knows something about the unique values
for this column. Is there some way I can get a list of the unique
values directly out of an index? So far, I haven't come across any
documentation that suggests this is possible, so I'm guessing there's no
straightforward way to do this...can anybody suggest some alternative
methods for summarizing the possible values from an indexed column that
has a small number of unique values?
Thanks in advance,
Mike