Re: Help with query: indexes on timestamps

Keith C. Perry wrote:
>
> 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