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.
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.> SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50
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.
----- Original Message ----- From: "Ragnar Hafstaš" <[EMAIL PROTECTED]>
Cc: "Andrei Bintintan" <[EMAIL PROTECTED]>; <firstname.lastname@example.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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match