|
|
 | | From: | Andrei Bintintan | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | Thu, 20 Jan 2005 15:45:47 +0200 |
|
|
 | > If you're using this to provide "pages" of results, could you use a
> cursor?
What do you mean by that? Cursor?
Yes I'm using this to provide "pages", but If I jump to the last pages it
goes very slow.
Andy.
----- Original Message -----
From: "Richard Huxton"
To: "Andrei Bintintan"
Cc: ;
Sent: Thursday, January 20, 2005 2:10 PM
Subject: Re: [SQL] OFFSET impact on Performance???
> Andrei Bintintan wrote:
>> Hi to all,
>>
>> I have the following 2 examples. Now, regarding on the offset if it
>> is small(10) or big(>50000) what is the impact on the performance of
>> the query?? I noticed that if I return more data's(columns) or if I
>> make more joins then the query runs even slower if the OFFSET is
>> bigger. How can I somehow improve the performance on this?
>
> There's really only one way to do an offset of 1000 and that's to fetch
> 1000 rows and then some and discard the first 1000.
>
> If you're using this to provide "pages" of results, could you use a
> cursor?
>
> --
> Richard Huxton
> Archonet Ltd
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Richard Huxton | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | Thu, 20 Jan 2005 15:20:59 +0000 |
|
|
| Andrei Bintintan wrote:
>> If you're using this to provide "pages" of results, could you use a
>> cursor?
>
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages
> it goes very slow.
DECLARE mycursor CURSOR FOR SELECT * FROM ...
FETCH FORWARD 10 IN mycursor;
CLOSE mycursor;
Repeated FETCHes would let you step through your results. That won't
work if you have a web-app making repeated connections.
If you've got a web-application then you'll probably want to insert the
results into a cache table for later use.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Ron Mayer | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | Thu, 20 Jan 2005 08:49:39 -0800 |
|
|
| Richard Huxton wrote:
>
> If you've got a web-application then you'll probably want to insert the
> results into a cache table for later use.
>
If I have quite a bit of activity like this (people selecting 10000 out
of a few million rows and paging through them in a web browser), would
it be good to have a single table with a userid column shared by all
users, or a separate table for each user that can be truncated/dropped?
I started out with one table; but with people doing 10s of thousand
of inserts and deletes per session, I had a pretty hard time figuring
out a reasonable vacuum strategy.
Eventually I started doing a whole bunch of create table tmp_XXXX
tables where XXXX is a userid; and a script to drop these tables - but
that's quite ugly in a different way.
With 8.0 I guess I'll try the single table again - perhaps what I
want may be to always have a I/O throttled vacuum running... hmm.
Any suggestions?
|
|
| | From: | Richard Huxton | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | Thu, 20 Jan 2005 17:04:23 +0000 |
|
|
| Ron Mayer wrote:
> Richard Huxton wrote:
>
>>
>> If you've got a web-application then you'll probably want to insert
>> the results into a cache table for later use.
>>
>
> If I have quite a bit of activity like this (people selecting 10000 out
> of a few million rows and paging through them in a web browser), would
> it be good to have a single table with a userid column shared by all
> users, or a separate table for each user that can be truncated/dropped?
>
> I started out with one table; but with people doing 10s of thousand
> of inserts and deletes per session, I had a pretty hard time figuring
> out a reasonable vacuum strategy.
As often as you can, and make sure your config allocates enough
free-space-map for them. Unless, of course, you end up I/O saturated.
> Eventually I started doing a whole bunch of create table tmp_XXXX
> tables where XXXX is a userid; and a script to drop these tables - but
> that's quite ugly in a different way.
>
> With 8.0 I guess I'll try the single table again - perhaps what I
> want may be to always have a I/O throttled vacuum running... hmm.
Well, there have been some tweaks, but I don't know if they'll help in
this case.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
|
|
