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.


----- Original Message ----- From: "Ragnar Hafstaš" <[EMAIL PROTECTED]>
To: <>
Cc: "Andrei Bintintan" <[EMAIL PROTECTED]>; <>
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

Reply via email to