On Tue, Nov 16, 2004 at 10:39:10AM +1100, Harvey, Allan AC wrote: > mill2=> explain select value from history where pointname = 'MILL2-SPEED' and dt < now() - interval '5 minutes'\g We were looking for the output from "explain analyze select ...." With EXPLAIN ANALYZE we can see how realistic the planner's estimates were. > Seq Scan on history (cost=0.00..8263.19 rows=9342 width=8) > Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone < (now() - '00:05:00'::interval))) You declared dt to be TIMESTAMP WITHOUT TIME ZONE, so its index won't be used because the filter's type is TIMESTAMP WITH TIME ZONE. Try casting the filter to dt's type by using now()::TIMESTAMP (this won't be necessary in 8.0). Aside from the type issue, the planner estimates that the query will return 9342 rows, so even if it could use an index it might think a sequential scan will be faster. The output from EXPLAIN ANALYZE would tell us if that guess is correct. Please show us the output of EXPLAIN ANALYZE after you've modified the query to use now()::TIMESTAMP. If the query still does a sequential scan then execute "SET enable_seqscan TO off", run EXPLAIN ANALYZE again, and show us that output as well. > On using BETWEEN:- > mill2=> select value from history where pointname = 'MILL2-SPEED' and dt between now() and now() - interval '5 minutes'\g > value > ------- > (0 rows) "a BETWEEN x AND y" is equivalent to "a >= x AND a <= y", so if x and y aren't chosen correctly then the expression will always evaluate to false: SELECT 5 BETWEEN 1 AND 10; ?column? ---------- t SELECT 5 BETWEEN 10 AND 1; ?column? ---------- f -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
