Re: Why count(*) doest use index?

On Mon, Mar 7, 2011 at 5:58 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> > 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;
>
> 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;
>

Postgresql is smart enough to know "x = 1 and y = 2" is the same as "(x, y)
= (1, 2)". Either way you get an index scan at best--better than a seq
scan, to be sure, but still expensive when you have a lot of data per (user,
month) and you're doing a lot of these queries.

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.
>

It's designed with this in mind.

--
Glenn Maynard