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: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 3:54 PM To: Shea,Dan [CIS] Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
