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) );
Do you really have to use numeric as primary key? Integer datatypes (int4/int8) are much faster than numeric.
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?
Over 25000 rows match your condition: timestamp_in < to_timestamp( '20031201', 'YYYYMMDD' );
How many rows do you have in your table? It's possible, that seq scan is just faster than using index when getting so many rows output.
Regards, Tomasz Myrta
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
