Skip site navigation (1) Skip section navigation (2)

Queries with timestamp, 2

From: "Arnau Rebassa i Villalonga" <arebassa(at)andromeiberica(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Queries with timestamp, 2
Date: 2004-01-23 07:50:02
Message-ID: 009b01c3e185$8255d8f0$3c0aa8c0@iberica.andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

  First of all thanks to Josh and Richard for their replies. What I have
done to test
their indications is the following. I have created a new table identical to
STATISTICS,
and an index over the TIMESTAMP_IN field.

CREATE TABLE STATISTICS2
(
  STATISTIC_ID    NUMERIC(10) NOT NULL DEFAULT
                  NEXTVAL('STATISTIC_ID_SEQ')
                  CONSTRAINT pk_st_statistic2_id PRIMARY KEY,
  TIMESTAMP_IN    TIMESTAMP,
  VALUE           NUMERIC(10)
);

CREATE INDEX i_stats2_tin ON STATISTICS2(TIMESTAMP_IN);

After that I inserted the data from STATISTICS and vacuumed the DB:

  INSERT INTO STATISTICS2 ( SELECT * FROM STATISTICS );
  vacuumdb -f -z -d test

once the vacuum has finished I do the following query

explain analyze select * from statistics2 where timestamp_in <
to_timestamp( '20031201', 'YYYYMMDD' );
NOTICE:  QUERY PLAN:

Seq Scan on statistics2  (cost=0.00..638.00 rows=9289 width=35) (actual
time=0.41..688.34 rows=27867 loops=1)
Total runtime: 730.82 msec

That query is not using the index. Anybody knows what I'm doing wrong?

Thank you very much

--
Arnau



pgsql-performance by date

Next:From: Bruce MomjianDate: 2004-01-23 15:01:09
Subject: Re: [PERFORM] Postgres on Netapp
Previous:From: Pavel StehuleDate: 2004-01-23 07:35:27
Subject: Re: Trigger performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group