From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Blakeley <mike(at)blakeley(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: group by week (ww), plus -S performance |
Date: | 2000-05-29 04:54:13 |
Message-ID: | 10002.959576053@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Michael Blakeley <mike(at)blakeley(dot)com> writes:
> SELECT to_date(date_part('year',stamp),'yyyy')-5+7*date_part('week',stamp)
> as week,count(*) FROM EVENTS GROUP BY week ORDER BY week;
Seems like the hard way. You are relying on an implicit conversion from
the float8 output of date_part() to the text input to_date expects,
which is kind of slow (and IMHO it's a bug in 7.0 that it will do such
a conversion silently, anyhow). Better to use date_trunc to save the
conversion step:
SELECT date_trunc('year',stamp)-5+7*date_part('week',stamp)
as week,count(*) FROM EVENTS GROUP BY week ORDER BY week;
But the real problem is that this is going to use a start-of-week day
that is offset five days from whatever day-of-the-week 1 January is.
If you'd tried a few other years than 2000 you'd likely have been
dissatisfied with the results...
It seems like the right answer is that date_trunc('week',stamp) ought
to do what you want, but it doesn't seem to be implemented. That's
definitely a shortcoming --- anyone want to fix it?
> The order-by clause doesn't seem to add much overhead - the query
> plan is the same with or without it.
Right, the GROUP BY requires a sort anyway, so the planner knows
there's no need to sort again on the same key.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Blakeley | 2000-05-29 06:12:48 | Re: group by week (ww), plus -S performance |
Previous Message | Tom Lane | 2000-05-29 04:16:08 | Re: Vacuum Complains |