From: Christopher Jackson <crjackso@gmail.com>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-performance@postgresql.org list <pgsql-performance@postgresql.org>
Subject: Re: Slow Count-Distinct Query
Date: Tue, 1 Apr 2014 23:22:14 -0500
Message-ID: <CAN81C180UgqVEpnO2OVjzr_RZ7HCvgM-Uj-gnbBWXzDF62bM+g@mail.gmail.com> (raw)
In-Reply-To: <CAGrpgQ9ga0N0cF8+Z0ZW8t1mgS=3v1igVdaiciz-Q5Z7ddfOyA@mail.gmail.com>
References: <CAN81C19-crDaZF3QysefWqL8qNSPZnJu2HY-ys4C-Szpd7LBvw@mail.gmail.com>
<CAGrpgQ9ga0N0cF8+Z0ZW8t1mgS=3v1igVdaiciz-Q5Z7ddfOyA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@gmail.com> wrote:
>
> 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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: crjackso@gmail.com, bricklen@gmail.com
Subject: Re: Slow Count-Distinct Query
In-Reply-To: <CAN81C180UgqVEpnO2OVjzr_RZ7HCvgM-Uj-gnbBWXzDF62bM+g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox