On Mon, Mar 7, 2011 at 4:26 PM, Glenn Maynard <glenn@zewt.org> wrote:
> On Mon, Mar 7, 2011 at 4:35 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> SELECT COUNT(*) FROM table WHERE expr;
>>
>> will use index (assuming expr is optimizable and is worth while to
>> optimize). Your case might be interesting for cache purposes if expr2
>> is expensive, but has nothing to do with postgres index usage via
>> count(*). mysql/myisam needs to scan as well in this case -- it
>> can't magically 'look up' the value as it can for the in filtered
>> (very special) case... it only differs from pg in that it can skip
>> heap visibility check because all records are known good (and pg is
>> moving towards optimizing this case in mostly read only workloads!)
>
> It'll do an index scan, but it's still a scan--linear time over the size of
> the set. That's too expensive for many cases.
>
> My particular case is something like this:
>
> SELECT COUNT(*), event_time::date FROM events
> WHERE event_time::date >= '2011-01-01' AND event_time::date < '2011-02-01'
> AND user=50
> GROUP BY event_time::date;
>
> 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.
create index on events(user, (event_time::date));
select count(*) from events
where
(user, event_time::date) >= (50, '2011-01-01')
and (user, event_time::date) < (50, '2011-02-01')
group by event_time::date;
Note the create index will only work above if event_time is of
timestamp (not timestamptz) because of time zone dependency. Any ad
hoc caching would also have the same problem, if users from different
time zones were hitting the cache -- they could get the wrong answer.
merlin
