Re: Why count(*) doest use index?

[Glenn Maynard <glenn@xxxxxxxx>]

On Mon, Mar 7, 2011 at 5:58 PM, Merlin Moncure <mmoncure@xxxxxxxxx> 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