Re: [PERFORM] [SQL] OFFSET impact on Performance???

[Andrei Bintintan]

Now I read all the posts and I have some answers.
Yes, I have a web aplication.
I HAVE to know exactly how many pages I have and I have to allow the user to 
jump to a specific page(this is where I used limit and offset). We have this 
feature and I cannot take it out.


> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
Now this solution looks very fast, but I cannot implement it, because I 
cannot jump from page 1 to page xxxx only to page 2. Because I know with 
this type where did the page 1 ended. And we have some really complicated 
where's and about 10 tables are involved in the sql query.

About the CURSOR I have to read more about them because this is my first 
time when I hear about.
I don't know if temporary tables are a solution, really I don't think so, 
there are a lot of users that are working in the same time at the same page.

So... still DIGGING for solutions.
Andy.
----- Original Message ----- 
From: "Ragnar Hafstaš" <[EMAIL PROTECTED]>
To: <pgsql-performance@postgresql.org>
Cc: "Andrei Bintintan" <[EMAIL PROTECTED]>; <pgsql-sql@postgresql.org>
Sent: Thursday, January 20, 2005 9:23 PM
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???


On Thu, 2005-01-20 at 19:12 +0000, Ragnar Hafstaš wrote:
On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
> The best way to do pages for is not to use offset or cursors but to use 
> an
> index. This only works if you can enumerate all the sort orders the
> application might be using and can have an index on each of them.
>
> To do this the query would look something like:
>
> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
>
> Then you take note of the last value used on a given page and if the 
> user
> selects "next" you pass that as the starting point for the next page.

this will only work unchanged if the index is unique. imagine , for
example if you have more than 50 rows with the same value of col.
one way to fix this is to use ORDER BY col,oid
and a slightly more complex WHERE clause as well, of course
gnari


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])