|
|
 | | From: | Merlin Moncure | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | Fri, 21 Jan 2005 08:33:24 -0500 |
|
|
 | > 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
|
| | From: | Greg Stark | | Subject: | Re: [SQL] OFFSET impact on Performance??? | | Date: | 21 Jan 2005 11:22:56 -0500 |
|
|
|
> > 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
|
