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

> 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.

If your working set is small, say a couple hundred records at the most
(web form or such), limit/offset may be ok. However you are already
paying double because you are extracting the # of records matching your
where clause, yes? Also, this # can change while the user is browsing,
heh.

IOW, your application code is writing expensive checks that the database
has to cash.

> >> > 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.

Cursors held by a connection. If your web app keeps persistent
connection, you can use them. In this case, pass the where clause to a
plpgsql function which returns a composite object containing a refcursor
object and the number of rows (read the docs!). If/When pg gets shared
cursors, this may be the way to go...but in this case you may have to
worry about closing them.

Without a connection, you need some type of persistence on the database.
This is complex but it can be done...but it will not be faster than
limit offset for browsing relatively small sets.

Merlin