Re: why doesn't an index help my simple query?

On Fri, 30 May 2003, Peter Bierman wrote:

> I am indeed a novice, but I've done plenty of googling for an answer,
> and haven't had any new ideas in a while, so I thought I'd ask...
>
> I have what I would think is a very simple database. I use it to log
> temperature probe readings. 8 different probes are recorded each
> minute. There are two tables, one of which maps probe-id's to more
> info about that probe, and another that holds all of the logged data,
> as time, probe-id, and temp.
>
> There is an index on the time column. Vacuum Analyze is run every night.
>
> As you can see below, using an index doesn't seem to have any
> significant impact on the query speed. Why not? I would expect a
> b-tree index to be amazingly fast for this sort of query. Why doesn't
> the index-scan stop once the filter threshold is crossed, since the
> index is sorted (right?)?

I haven't gotten much into dbase issues, so I can't help you
on that. But, maybe you aren't really looking for the right
things. What sorts of things are you hoping to find in your
temperature data?

If your probes are all at the same temperature, then indexing
on temperature really isn't going to do any good. There is
only a single value which is trying to map to everything.

Now, if you collect data every N (minutes/hours/days/...),
what might be more useful, would be to every hour (or day,
some convenient time period) gather all the data for the
last (hour/day/week/..., whatever is useful) time period
of interest and process that data in some way. In the
old days, I suppose a Fourier transform (probably a FFT)
would be the main choice. These days, there might be
other things like wavelets for how to transform the data.
In any event, what you might be looking for is changes
happening at specific frequencies. So, making another
table to store some of the results from Fourier analysis
would allow you to construct queries as to how the magnitude
of a certain frequency changed with time.

For example:
I get temperature readings from one of my disk drives.
And every hour, I get a message in my logs telling me
about the temperature. I haven't done anything yet,
but what I should do is to keep track of this log data,
as well as visit a local weather station and download
local temperature outside my house. Then I have a chance
to partition the observed disk drive temperature into
some amount due to external temperature and some due
to things due to disk operation. I might then be able
to construct a query which looks at the DC (wavelength
of infinity) component to tell me about how the drive
temperature increases with time. I would expect this
value to be constant, until the end of drive life
was approaching due to things like bearings. I might
add in things like the number of disk accesses per hour,
to try and correlate temperature increases to things
like updating the locate database (filenames present on
system).

There's lots of things one can do. But it may be that
you are looking for something involving temperature,
and not temperature by itself. And sitting down and
figuring out what might be happening to change
temperature values, might help you come up with
fields in this, or other tables, which would allow
the use of things like indexes to work better.

Gord
--
Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (alt.)