X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7B18BD1B456 for ; Mon, 26 Jan 2004 15:01:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 99618-02 for ; Mon, 26 Jan 2004 11:00:58 -0400 (AST) Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66]) by svr1.postgresql.org (Postfix) with ESMTP id A50E8D1D14B for ; Mon, 26 Jan 2004 11:00:48 -0400 (AST) Received: from klaster.net (backbone.zab.citynet.pl [212.244.6.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id B05682B3D2; Mon, 26 Jan 2004 15:59:53 +0100 (CET) Message-ID: <40152BD9.3050907@klaster.net> Date: Mon, 26 Jan 2004 16:01:45 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.6b) Gecko/20031208 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Arnau Cc: pgsql-performance@postgresql.org Subject: Re: Queries with timestamp II References: <049101c3e416$74023120$3c0aa8c0@iberica.andromeiberica.com> In-Reply-To: <049101c3e416$74023120$3c0aa8c0@iberica.andromeiberica.com> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/267 X-Sequence-Number: 5441 Dnia 2004-01-26 15:12, Użytkownik Arnau napisał: > 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