Re: table configuration tweak for performance gain.

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/