pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Queries with timestamp II
3+ messages / 3 participants
[nested] [flat]

* Queries with timestamp II
@ 2004-01-26 14:12 Arnau <arnaulist@andromeiberica.com>
  2004-01-26 14:38 ` Re: Queries with timestamp II Shridhar Daithankar <shridhar@frodo.hserus.net>
  2004-01-26 15:01 ` Re: Queries with timestamp II Tomasz Myrta <jasiek@klaster.net>
  0 siblings, 2 replies; 3+ messages in thread

From: Arnau @ 2004-01-26 14:12 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Queries with timestamp II
  2004-01-26 14:12 Queries with timestamp II Arnau <arnaulist@andromeiberica.com>
@ 2004-01-26 14:38 ` Shridhar Daithankar <shridhar@frodo.hserus.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Shridhar Daithankar @ 2004-01-26 14:38 UTC (permalink / raw)
  To: pgsql-performance

Arnau wrote:

> 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?

Since it expects large number of rows will be returned, it is favouring 
sequential scan.

Given how the estimates have differed, i.e. estimated 9289 v/s actual 27867, I 
sugest you up the statistics for the table using alter table. Check
http://www.postgresql.org/docs/current/static/sql-altertable.html


HTH

  Shridhar



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Queries with timestamp II
  2004-01-26 14:12 Queries with timestamp II Arnau <arnaulist@andromeiberica.com>
@ 2004-01-26 15:01 ` Tomasz Myrta <jasiek@klaster.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Tomasz Myrta @ 2004-01-26 15:01 UTC (permalink / raw)
  To: Arnau <arnaulist@andromeiberica.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-01-26 15:01 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-26 14:12 Queries with timestamp II Arnau <arnaulist@andromeiberica.com>
2004-01-26 14:38 ` Shridhar Daithankar <shridhar@frodo.hserus.net>
2004-01-26 15:01 ` Tomasz Myrta <jasiek@klaster.net>

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