Andrei Bintintan wrote:
> Hi to all,
>
> I have the following 2 examples. Now, regarding on the offset if it
> is small(10) or big(>50000) what is the impact on the performance of
> the query?? I noticed that if I return more data's(columns) or if I
> make more joins then the query runs even slower if the OFFSET is
> bigger. How can I somehow improve the performance on this?

There's really only one way to do an offset of 1000 and that's to fetch
1000 rows and then some and discard the first 1000.

If you're using this to provide "pages" of results, could you use a cursor?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Alex Turner wrote:
> I am also very interesting in this very question.. Is there any way
> to declare a persistant cursor that remains open between pg sessions?

Not sure how this would work. What do you do with multiple connections?
Only one can access the cursor, so which should it be?

> This would be better than a temp table because you would not have to
> do the initial select and insert into a fresh table and incur those
> IO costs, which are often very heavy, and the reason why one would
> want to use a cursor.

I'm pretty sure two things mean there's less difference than you might
expect:
1. Temp tables don't fsync
2. A cursor will spill to disk beyond a certain size

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Andrei Bintintan" <klodoma@ar-sd.net> writes:

> > If you're using this to provide "pages" of results, could you use a cursor?
> What do you mean by that? Cursor?
>
> Yes I'm using this to provide "pages", but If I jump to the last pages it goes
> very slow.

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 query takes the same amount of time no matter how many records are in the
table and no matter what page of the result set the user is on. It should
actually be instantaneous even if the user is on the hundredth page of
millions of records because it uses an index both for the finding the right
point to start and for the ordering.

It also has the advantage that it works even if the list of items changes as
the user navigates. If you use OFFSET and someone inserts a record in the
table then the "next" page will overlap the current page. Worse, if someone
deletes a record then "next" will skip a record.

The disadvantages of this are a) it's hard (but not impossible) to go
backwards. And b) it's impossible to give the user a list of pages and let
them skip around willy nilly.


(If this is for a web page then specifically don't recommend cursors. It will
mean you'll have to have some complex session management system that
guarantees the user will always come to the same postgres session and has some
garbage collection if the user disappears. And it means the URL is only good
for a limited amount of time. If they bookmark it it'll break if they come
back the next day.)

--
greg


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

Alex Turner <armtuk@gmail.com> writes:

> I am also very interesting in this very question.. Is there any way to
> declare a persistant cursor that remains open between pg sessions?
> This would be better than a temp table because you would not have to
> do the initial select and insert into a fresh table and incur those IO
> costs, which are often very heavy, and the reason why one would want
> to use a cursor.

TANSTAAFL. How would such a persistent cursor be implemented if not by
building a temporary table somewhere behind the scenes?

There could be some advantage if the data were stored in a temporary table
marked as not having to be WAL logged. Instead it could be automatically
cleared on every database start.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Alex Turner wrote:
> As I read the docs, a temp table doesn't solve our problem, as it does
> not persist between sessions. With a web page there is no guarentee
> that you will receive the same connection between requests, so a temp
> table doesn't solve the problem. It looks like you either have to
> create a real table (which is undesirable becuase it has to be
> physicaly synced, and TTFB will be very poor) or create an application
> tier in between the web tier and the database tier to allow data to
> persist between requests tied to a unique session id.
>
> Looks like the solutions to this problem is not RDBMS IMHO.

It's less the RDBMS than the web application. You're trying to mix a
stateful setup (the application) with a stateless presentation layer
(the web). If you're using PHP (which doesn't offer a "real" middle
layer) you might want to look at memcached.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster