limit-offset different result sets with same query

Hi all.

I'll make this faster.

I hace this table and this function:

CREATE FUNCTION pg_round_random_range(integer, integer) RETURNS integer
LANGUAGE plperl IMMUTABLE STRICT
AS $_X$
my($imin, $imax) = @_;
if ($_[0] == $_[1]){
return $_[0];}
if($imin > $imax){
$imin = $_[1];
$imax = $_[0];}
$_number_ = ( (rand) * ($imax + 1));
while (($_number_ < $imin) && ( $_number_ > $imax)){
$_number_ = ( (rand) * ($imax + 1));}
return sprintf "%d",$_number_;

$_X$;

CREATE TABLE datos (
texto text DEFAULT md5((random())::text),
entero2 smallint DEFAULT (rpad((hashtext((random())::text))::text,
4))::smallint,
entero4 integer DEFAULT (lpad(((hashtext((random())::text))::text
|| replace((hashtext((random())::text))::text, '-'::text, ''::text)),
9))::integer,
entero8 bigint DEFAULT (rpad(((hashtext((random())::text))::text
|| replace((hashtext((random())::text))::text, '-'::text, ''::text)),
19))::bigint,
"float" double precision DEFAULT ((random() * (1000)::double
precision) + random()),
fecha date DEFAULT (now())::date,
tiempo timestamp without time zone DEFAULT now(),
ztiempo timestamp with time zone DEFAULT now(),
ip cidr DEFAULT ((((((((pg_round_random_range(0, 255))::text ||
'.'::text) || (pg_round_random_range(0, 255))::text) || '.'::text) ||
(pg_round_random_range(0, 255))::text) || '.'::text) ||
(pg_round_random_range(0, 255))::text))::cidr
);

I insert several tuples to make a test with the sentence 'insert into
datos default values'.
This table don't have indexes. There are no users connected exept me
(is a local and
test database).
When i have ~160000 regs i start to make some querys.

Executing 'select * from datos limit 1 offset 150000' two times i have different
result sets.
When i execute 'explain analyze verbose <query>' i see that (as
expected) the seq scan
is occurring.

Examples:

parapruebas=# select entero8 from datos limit 1 offset 2;
entero8
--------------------
477808241937806077
(1 row)

parapruebas=# select entero8 from datos limit 1 offset 2;
entero8
--------------------
477808241937806077
(1 row)
QUERY PLAN

----------------------------------------------------------------------------------------------------
Limit (cost=0.05..0.07 rows=1 width=8) (actual time=0.033..0.036
rows=1 loops=1)
-> Seq Scan on datos (cost=0.00..4128.00 rows=172800 width=8)
(actual time=0.014..0.020 rows=3
loops=1)
Total runtime: 0.107 ms
(3 rows)

In this case, on a small offset the result set returns the same. But
in higher offsets:

parapruebas=# select entero8 from datos limit 1 offset 100000;
entero8
--------------------
-82136193203177195
(1 row)

parapruebas=# select entero8 from datos limit 1 offset 100000;
entero8
---------------------
1201794554456297856
(1 row)
QUERY PLAN

----------------------------------------------------------------------------------------------------
Limit (cost=2388.89..2388.91 rows=1 width=8) (actual
time=622.198..622.201 rows=1 loops=1)
-> Seq Scan on datos (cost=0.00..4128.00 rows=172800 width=8)
(actual time=0.014..356.800 rows=
100001 loops=1)
Total runtime: 622.247 ms
(3 rows)

That's correct? Is logical that if the scan is sequential in the
physical table returns differents
data?

I test it on 8.4 beta1 and 8.3.5 and 8.3.7 with the same results.

Thanks in advance.

--
Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member