Re: Is it possible to summarize uniqe values from an indexed column?

On Fri, Apr 28, 2006 at 08:35:08AM -0400, Mike Leahy wrote:
> 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;'.

I don't know if you can specify it in plain SQL, but you might be able
to code the following into a pl/pgsql function (this is pseudo-code):

function getunique
$curr = "";
loop:
select $next from table where field > $curr order by field limit 1;
if found then
return $next
$curr = $next;
goto loop;
return;

The idea being that you ask the index lookup to find the next biggest
item. You end up doing lots of little queries but on big table with
lots of duplicates it might be a win.

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.