Skip site navigation (1) Skip section navigation (2)

Re: Counting unique rows as an aggregate.

From: r_musta <zepolen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Counting unique rows as an aggregate.
Date: 2008-09-30 06:59:50
Message-ID: 7c9ccfff-d4ce-4c2f-8258-8cf1ceb22a27@q9g2000hsb.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
On Sep 29, 11:25 pm, scott(dot)marl(dot)(dot)(dot)(at)gmail(dot)com ("Scott Marlowe") wrote:
> > However, this is starting to become too slow (as there are about 10 of
> > these queries), and therefore I need to write an aggregate function
> > which lets me do:
>
> >>SELECT count_unique(make), count_unique(color) from table WHERE >criteria<;
>
> > After reading about aggregate functions, this should be possible, as
> > long as I can use a dictionary/hashmap type for the state<STYPE>
> > argument.
>
> This might be a nice fit for materialized views.  While they're not
> built in, PostgreSQL's extensibility allows you to build them prettily
> easily.
>
> http://jonathangardner.net/tech/w/PostgreSQL/Materialized_Views

Materialized views won't work as the criteria used to narrow the
search is very arbritrary and rarely repeated, and as the count's
depend on the current result set, they would offer a miniscule
increase in speed, only as the row width would be a little smaller.

In response to

pgsql-general by date

Next:From: Abdul RahmanDate: 2008-09-30 07:22:23
Subject: Re: Replication using slony-I
Previous:From: r_mustaDate: 2008-09-30 06:55:46
Subject: Re: Counting unique rows as an aggregate.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group