I have a table with with 1 million records in it. Here is the definition
CREATE TABLE report
(
match int4,
action varchar(16),
stamp timestamptz,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text
) WITHOUT OIDS;
The is one index:
CREATE INDEX stamp_idx ON report USING btree (stamp);
That query I'm running is:
SELECT date_part('hour'::text, report.stamp) AS "hour", count(*) AS count FROM report GROUP BY date_part('hour'::text, report.stamp) ORDER BY date_part('hour'::text, report.stamp);
You will always get a sequential scan with this query - there is no other way to count the rows.
With PostgreSQL being MVCC based, you can't know whether a row is visible to you without checking it - visiting the index won't help. Even if it could, you'd still have to visit every row in the index.
Assuming the table is a log, with always increasing timestamps, I'd create a summary table and query that.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
