limit-offset different result sets with same query - Mailing list pgsql-general

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