Re: group by week (ww), plus -S performance

Michael Blakeley <mike@blakeley.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