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