I have a big table that is used for datalogging. I'm designing
graphing interface that will visualise the data. When the user is
looking at a small daterange I want the database to be queried for all
records, but when the user is 'zoomed out', looking at an overview, I
want run a query that skips every nth record and returns a managable
dataset that still gives a correct overview of the data without
slowing the programme down. Is there an easy way to do this that I
have overlooked? I looked at:
1. taking the primary key (which is an auto increasing integer) and
running modulo on it. This will not work in my case as the database is
being used for a number of different logging 'runs', and these runs
are not necessarily equally distributed over the primary keys.
2. Subqueries that do SELECT COUNT to generate a row number are too
slow as im dealing with thousands to tens of thousands of records.
3. My last idea was to create a sequence with CYCLE and min_Value 0
and max_value 1:
SELECT * FROM ( SELECT *, (SELECT nextval('counter_seq')) as counter
FROM table) WHERE counter = 0
this didnt work (counter was always the same for all rows), so i put
SELECT nextval('counter_seq') in a function called counter():
SELECT *, counter() as counter FROM table
this gives the table i'm looking for, however, I am unable to use
WHERE counter = 0. when I run EXPLAIN, it tells me that it is actually
not looking at the values in the table but just running the function
again to filter. So I tried this:
SELECT *, (counter()+id-id) as counter FROM table
where Id is the primary key of the table. im trying to fool the
interpreter into looking at the table instead of running the function
itself. Again, this query generates the right table. So, I tried
adding WHERE counter = 0. Again it doesnt work: it returns the same
number of rows, but changes the values of all rows in the 'counter'
column to 1. EXPLAIN does not help me (gives no information about the
filtering).
Any general thoughts on how to achieve my original goal or on how to
fix issues with my 3d attempt are appreciated.
Tom
