Re: database performance and query performance question

The end date in the previous example was actually invalid between
'2004-01-12'::date and '2003-01-12'::date;
There have been multiple inserts since I recreated the index but it took
quite some time to complete the following
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time
between '2004-01-12'::date and '2004-01-13'::date;

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------
Index Scan using forecastelement_v_idx on forecastelement
(cost=0.00..832139.81 rows=2523119 width=129) (actual time=0.519..467159.658
rows=2940600 loops=1)
Index Cond: ((valid_time >= '2004-01-12 00:00:00'::timestamp without time
zone) AND (valid_time <= '2004-01-13 00:00:00'::timestamp without time
zone))
Total runtime: 472627.148 ms
(3 rows)

-----Original Message-----
From: Shea,Dan [CIS]
Sent: Thursday, January 22, 2004 4:10 PM
To: 'Hannu Krosing'; Shea,Dan [CIS]
Cc: 'josh(at)agliodbs(dot)com'; pgsql-performance(at)postgresql(dot)org
Subject: RE: [PERFORM] database performance and query performance
question

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(at)tm(dot)ee]
Sent: Thursday, January 22, 2004 3:54 PM
To: Shea,Dan [CIS]
Cc: 'josh(at)agliodbs(dot)com'; pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org