I thought I'd pass along a work-around I came up with for the limits
in 'ww' support (7.0 final). Maybe this would be a useful example for
the docs? They're a little lean on date/time examples, IMO. So is the
new book.
Task:
Select a count of records from a table, grouped by the week of the
record. The table is something like
CREATE table EVENTS
(event varchar(128) not null, stamp datetime default now());
And I want the output to use human-readable dates, like
week | count
------------+-------
2000-03-06 | 4
2000-03-13 | 5
2000-03-20 | 3
My immediate solution was something like
SELECT to_date(date_part('year',stamp)||'-'||date_part('week',stamp),
'yyyy-ww'),count(*) FROM EVENTS GROUP BY to_date;
but to_date() doesn't seem to support 'ww' format for text-to-date
translation (not documented, AFAIK).
The solution I eventually found was
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;
I haven't tested the '-5' kludge very extensively. It seems to work
ok, for the dates I tested in 2000. I'm sure it would run into
trouble with calendar-change weirdness pre-1900.
I'd also love to hear any suggestions for performance improvements -
it's cpu-bound on my system, and takes about 70 sec to run with
86,000 rows.
The query plan is
Aggregate (cost=9155.76..9584.66 rows=8578 width=20)
-> Group (cost=9155.76..9370.21 rows=85780 width=20)
-> Sort (cost=9155.76..9155.76 rows=85780 width=20)
-> Seq Scan on events (cost=0.00..2126.80 rows=85780 width=20)
The order-by clause doesn't seem to add much overhead - the query
plan is the same with or without it.
I'm running with "-i -N 64 -B 1024 -o '-F'", and I've tried up to '-S
8192' without seeing any noticeable improvement. At higher values,
performance actually went down by almost 50% - something to do with
shmem segment sizes? This is on Solaris 2.6, and I compiled PG7.0
using gcc 2.95.
The -S does reduce disk I/O, but I think that's only about 5% of the
work that's going on (90% user time). An index on 'stamp' doesn't
seem to help, either.
thanks,
-- Mike
