To: pgsql-general@postgresql.org
Subject: Re: Result set granularity..
References: <59410.193.243.134.6.1064668289.squirrel@www.defero.se>
In-Reply-To: <59410.193.243.134.6.1064668289.squirrel@www.defero.se>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 29 Sep 2003 11:12:52 -0400
Message-ID: <87u16veih7.fsf@stark.dyndns.tv>
Lines: 47
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Short answer, no there's no trick to doing this in postgres like rownum. You
would have to either add a column with sequential or random keys and then
select on it, or else pull down all the rows and only process the ones you
want.

Rownum is handy though, I wonder how easy it would be to add it to postgres.


"Rasmus Aveskogh" <rasmus@defero.se> writes:

> SELECT <column> FROM <table> WHERE mod(rownum, 5) = 0;

Uhm. Does that actually work? I thought rownum only incremented for every row
actually returned. So that this would return one row and then stop returning
rows.

I would have thought you would have to use a subquery to get this to work
like:

SELECT * FROM (SELECT column, rownum AS n FROM table) WHERE mod(n,5)=0

> The query above would give me every fifth row of the original result set
> and would save me from sending the data over my database connection and do
> the lowering of the granularity in the application.

Also, in Oracle there's actually a SAMPLE keyword that you can put on a select
to tell oracle that you only need a sample. It's way more efficient than using
rownum because it skips whole blocks. Ie, the equivalent of above would be
'SAMPLE 20 PERCENT' or something like that, and it would read a whole block,
then skip 4 whole blocks.

However, for statistical purposes both of these techniques have downsides.
Consider the case where you're looking for the standard deviation of some
timing information and there's a spike every five minutes. The correlation
between the sampling and the event could create spurious results. You could
completely fail to see the events, or thing they are much worse than they are.
Or even that they're worse at some times of day when in fact they're
consistent.

Really what you want is to assign statistically random numbers, probably
floating point numbers, to each record, then read all records where those
numbers are in some range. Even that might not really be kosher for serious
statistics.

-- 
greg