This sure speed up the query, it is fast.
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time
between '2004-01-12'::date and '2003-01-12'::date;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
Index Scan using forecastelement_v_idx on forecastelement
(cost=0.00..159607.11 rows=466094 width=129) (actual time=49.504..49.504
rows=0 loops=1)
Index Cond: ((valid_time >= '2004-01-12 00:00:00'::timestamp without time
zone) AND (valid_time <= '2003-01-12 00:00:00'::timestamp without time
zone))
Total runtime: 49.589 ms
(3 rows)
-----Original Message-----
From: Hannu Krosing [mailto:hannu@tm.ee]
Sent: Thursday, January 22, 2004 3:54 PM
To: Shea,Dan [CIS]
Cc: 'josh@agliodbs.com'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] database performance and query performance
question
Hannu Krosing kirjutas N, 22.01.2004 kell 22:46:
> Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35:
> > Something that I do not understand is why if you use a valid_time =
> > '2004-01-22 00:00:00' the query will use the index but if you do a
> > valid_time > '2004-01-22 00:00:00' it does not use the index?
>
> It probably can't tell if > is selective enough to justify using index.
>
> Together with "limit 10" it may be.
>
> You could try
>
> explain analyze select * from forecastelement where valid_time between
> '2004-01-22'::date and '2004-01-22'::date limit 10;
Sorry, that should have been:
between '2004-01-22'::date and '2004-01-23'::date
> to see if this is considered good enough.
>
> --------------
> Hannu
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
