From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECTing every Nth record for better performance |
Date: | 2009-12-04 06:42:00 |
Message-ID: | 20091204064159.GB14099@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In response to Tom :
> 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:
Do you have 8.4? If yes:
test=# create table data as select s as s from generate_Series(1,1000) s;
SELECT
test=*# select s from (select *, row_number() over (order by s) from
data) foo where row_number % 3 = 0 limit 10;
s
----
3
6
9
12
15
18
21
24
27
30
(10 rows)
-- or skip every 5. record:
test=*# select s from (select *, row_number() over (order by s) from
data) foo where row_number % 5 != 0 limit 10;
s
----
1
2
3
4
6
7
8
9
11
12
(10 rows)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2009-12-04 10:07:58 | Re: [HACKERS] Installing PL/pgSQL by default |
Previous Message | A. Kretschmer | 2009-12-04 06:35:40 | Re: SELECTing every Nth record for better performance |