From: Tomasz Myrta <jasiek@klaster.net>
To: Arnau <arnaulist@andromeiberica.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Queries with timestamp II
Date: Mon, 26 Jan 2004 16:01:45 +0100
Message-ID: <40152BD9.3050907@klaster.net> (raw)
In-Reply-To: <049101c3e416$74023120$3c0aa8c0@iberica.andromeiberica.com>
References: <049101c3e416$74023120$3c0aa8c0@iberica.andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jasiek@klaster.net, arnaulist@andromeiberica.com
Subject: Re: Queries with timestamp II
In-Reply-To: <40152BD9.3050907@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox