Re: Slow Count-Distinct Query

Hi Bricklen,

Thanks for the feedback. I'll play around with materialized views. My
understanding is they have to be manually triggered for refresh and there's
an exclusive lock on the view while the refresh is taking place. Is this
your understanding as well? I'm using PG 9.3.3. If this is true, I'm
curious what clever ways people have come up with to mitigate any issues
with the lock.

Thanks again,
Chris

On Tue, Apr 1, 2014 at 7:34 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

>
> On Sun, Mar 30, 2014 at 12:45 PM, Christopher Jackson <crjackso(at)gmail(dot)com>wrote:
>
>> Hi all,
>>
>> tl;dr - How can I speed up my count-distinct query?
>>
>
> Depending on how often you need to run that query and how important it is
> to you, if you are willing to accept a performance hit on
> INSERT/UPDATE/DELETE of the "participants" table, you could create a
> summary table containing just the count of unique email addresses or the
> list of unique email addresses populated via trigger on
> INSERT/UPDATE/DELETE of the participants table. Another option is try out
> the new Materialized views (
> http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html)
> available in 9.3.
>
>