pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChanged to: how to solve the get next 100 records problem
3+ messages / 3 participants
[nested] [flat]
* Changed to: how to solve the get next 100 records problem
@ 2005-05-18 15:42 Alain <alainm@pobox.com>
0 siblings, 2 replies; 3+ messages in thread
From: Alain @ 2005-05-18 15:42 UTC (permalink / raw)
To: pgsql-sql; Diogenes <di@sene.info>
Hi Ragnar (and others),
I found something that is both fast and simple (program side):
Allways assuming that: pkey is a primary key and skey is a sort key, and
there exists an index on (skey,pkey)
first select is
select ... from tab ORDER by skey,pkey LIMIT 100;
subsequent selects are
(select ... from tab WHERE skey=skey_last AND pkey>pkey_last
ORDER BY skey,pkey LIMIT 100)
UNION
(select ... from tab WHERE skey>skey_last
ORDER BY skey,pkey LIMIT 100)
ORDER BY skey,pkey LIMIT 100;
The catch is that if the first select would have more than 100 records
and was limited to 100, the second select's data is completeply
discarted by the 3rd limit!
The only strange thing is that without the 3rd order by, the order is
wrong. I didn't expect it because each select is created ordered. Is it
expected that UNION mixes it all up? (using postgre 7.4.1)
The 3rd order by is not indexed, but it operates in a memory table of no
more than 200 so it is fast too.
Please comment on this. I tested and it worked but I really new to sql
and I feel insecure...
Thanks,
Alain
>>
>>>>[how to solve the get next 100 records problem]
>
>>BUT, I think that this is close to a final solution, I made some
>>preliminary test ok. Please tell me what you think about this.
>>
>>Fisrt let's state that I am reading records to put on a screen (in a
>>Table/Grid). I separated the problem is *3* parts
>>
>>-first select is as above:
>>select ... from tab ORDER by skey,pkey LIMIT 100;
>>
>>-second method for next 100:
>>select ... from tab WHERE skey>=skey_last
>> ORDER BY skey,pkey
>> LIMIT 100;
>>but here I test for repetitions using pkey and discard them
>>
>>-now if I get all repetitions or the last 100 have the same skey with
>>the second method, I use
>>select ... from tab WHERE skey=skey_last AND pkey>pkey_last
>> ORDER BY skey,pkey
>> LIMIT 100;
>>until I get an empty response, then I go back to the second method.
>
>
> if your distribution is such that those skeys that have > 100 records
> tend to have a lot more, you might have a higher limit for this case.
>
>
>
>>All queries are extremely fast with 6000000 records and it looks like
>>the few redundant or empty queries (but very fast) will not be a problem.
>>
>>What is your opinion about this (apart that it is a bit complex :) ??
>
>
>
> looks fine
>
> gnari
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changed to: how to solve the get next 100 records problem
@ 2005-05-18 17:52 PFC <lists@boutiquenumerique.com>
parent: Alain <alainm@pobox.com>
1 sibling, 0 replies; 3+ messages in thread
From: PFC @ 2005-05-18 17:52 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; pgsql-sql; Diogenes <di@sene.info>
> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)
That's because UNION removes duplicates, which it will probably doing
using a hash (EXPLAIN ANALYZE is your friend).
Use UNION ALL because your WHERE condition allows no duplicates anyway.
UNION ALL preserves the order.
So you can get the LIMIT out of the subqueries and put it around the
UNION ALL.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Changed to: how to solve the get next 100 records problem
@ 2005-05-18 17:53 Ragnar Hafstað <gnari@simnet.is>
parent: Alain <alainm@pobox.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ragnar Hafstað @ 2005-05-18 17:53 UTC (permalink / raw)
To: Alain <alainm@pobox.com>; +Cc: pgsql-sql; Diogenes <di@sene.info>
On Wed, 2005-05-18 at 12:42 -0300, Alain wrote:
> I found something that is both fast and simple (program side):
> ...
> subsequent selects are
> (select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey LIMIT 100)
> UNION
> (select ... from tab WHERE skey>skey_last
> ORDER BY skey,pkey LIMIT 100)
> ORDER BY skey,pkey LIMIT 100;
> ...
> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)
ORDER BY on subselects are not garanteed by SQL to have any effect,
but Postgres tends to do what you want when possible and not
detrimental to performance.
In this case, Postgres would probably have kept the order had
you used UNION ALL
a plain UNION implies DISTINCT, which Postgres is free to
implement any way it wants, possibly destroying the order
in this case a UNION ALL is appropriate, as you know that
the 2 selects do not overlap. possibly, a future version
of the planner will be able to detect this.
in any case, the last ORDER BY LIMIT does not cost much, and it
protects you against implementation changes, and limits
the result to 100 records, which might be what you want.
> Please comment on this. I tested and it worked but I really new to sql
> and I feel insecure...
it's good.
gnari
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-18 17:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-18 15:42 Changed to: how to solve the get next 100 records problem Alain <alainm@pobox.com>
2005-05-18 17:52 ` PFC <lists@boutiquenumerique.com>
2005-05-18 17:53 ` Ragnar Hafstað <gnari@simnet.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox