Re: Help with query: indexes on timestamps - Mailing list pgsql-general

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