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.
On Sun, Mar 30, 2014 at 12:45 PM, Christopher Jackson <crjackso@gmail.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.