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.

I'm afraid you have a problem then. The only way postgres can know exactly how
many pages and allow users to jump to a specific point for an arbitrary query
is by doing what OFFSET and LIMIT does.

There are ways to optimize this but they'll be lots of work. And they'll only
amount to moving around when the work is done. The work of gathering all the
records from the query will still have to be done sometime.

If the queries are relatively static you could preprocess the data so you have
all the results in a table with a sequential id. Then you can get the maximum
and jump around in the table using an index all you want.

Otherwise you could consider performing the queries on demand and storing them
in a temporary table. Then fetch the actual records for the page from the
temporary table again using an index on a sequential id to jump around. This
might make the actual performing of the initial query much slower though since
you have to wait for the entire query to be performed and the records stored.
You'll also have to deal with vacuuming this table aggressively.

--
greg