Re: why doesn't an index help my simple query? - Mailing list pgsql-novice
From | |
---|---|
Subject | Re: why doesn't an index help my simple query? |
Date | |
Msg-id | Pine.A41.3.95.1030531000453.12720C-100000@fn2.freenet.edmonton.ab.ca Whole thread Raw |
In response to | why doesn't an index help my simple query? (Peter Bierman <bierman@apple.com>) |
List | pgsql-novice |
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.)
pgsql-novice by date: