pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Alex Turner <armtuk@gmail.com>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Wed, 26 Jan 2005 08:47:32 -0500
Message-ID: <33c6269f05012605473319ef40@mail.gmail.com> (raw)
In-Reply-To: <015a01c5038f$73a70720$0b00a8c0@forge>
References: <019f01c4fee1$1b58d600$0b00a8c0@forge>
	<41EF9FD3.5020400@archonet.com>
	<020d01c4fef6$595bb9d0$0b00a8c0@forge>
	<41EFCC5B.5010302@archonet.com>
	<33c6269f05012008391490448b@mail.gmail.com>
	<87y8eh1hy9.fsf@stark.xeocode.com>
	<015a01c5038f$73a70720$0b00a8c0@forge>

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.

Alex Turner
NetEconomist


On Wed, 26 Jan 2005 12:11:49 +0200, Andrei Bintintan <klodoma@ar-sd.net> wrote:
> The problems still stays open.
> 
> The thing is that I have about 20 - 30 clients that are using that SQL query
> where the offset and limit are involved. So, I cannot create a temp table,
> because that means that I'll have to make a temp table for each session...
> which is a very bad ideea. Cursors somehow the same. In my application the
> Where conditions can be very different for each user(session) apart.
> 
> The only solution that I see in the moment is to work at the query, or to
> write a more complex where function to limit the results output. So no
> replace for Offset/Limit.
> 
> Best regards,
> Andy.
> 
> 
> ----- Original Message -----
> From: "Greg Stark" <gsstark@mit.edu>
> To: <alex@neteconomist.com>
> Cc: "Richard Huxton" <dev@archonet.com>; "Andrei Bintintan"
> <klodoma@ar-sd.net>; <pgsql-sql@postgresql.org>;
> <pgsql-performance@postgresql.org>
> Sent: Tuesday, January 25, 2005 8:28 PM
> Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
> 
> 
> >
> > 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
> >
> >
> 
>



view thread (22+ messages)  latest in thread

Message-ID: <33c6269f05012605473319ef40@mail.gmail.com>
Permalink:  ../33c6269f05012605473319ef40@mail.gmail.com/
Also on:    postgresql.org/message-id/33c6269f05012605473319ef40@mail.gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: armtuk@gmail.com, klodoma@ar-sd.net, gsstark@mit.edu, dev@archonet.com, pgsql-performance@postgresql.org
  Subject: Re: [SQL] OFFSET impact on Performance???
  In-Reply-To: <33c6269f05012605473319ef40@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox