From: | "Igor Neyman" <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Glenn Maynard" <glenn(at)zewt(dot)org>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why count(*) doest use index? |
Date: | 2011-03-08 15:42:11 |
Message-ID: | F4C27E77F7A33E4CA98C19A9DC6722A2073C2C13@EXCHANGE.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: Glenn Maynard [mailto:glenn(at)zewt(dot)org]
> Sent: Monday, March 07, 2011 5:27 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: Why count(*) doest use index?
>
>
> An index on "events(user, event_time::date)" could optimize
> this, eg. effectively maintaining a count of matching rows
> for each (user, day) tuple--which is ultimately what I'm
> doing manually with triggers. Of course, it would have a
> significant cost, in some combination of complexity, index
> size and write concurrency, and couldn't be the default
> behavior for an index.
>
> --
> Glenn Maynard
>
Indexes don't "maintain counts", indexes maintain pointers to the table
records.
What you need is "materialized view" storing aggregates.
And it looks like you already have it with your triggers.
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-03-08 15:45:41 | Re: NULL value vs. DEFAULT value. |
Previous Message | Raymond O'Donnell | 2011-03-08 15:13:30 | Re: NULL value vs. DEFAULT value. |