pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Turner <armtuk@gmail.com>
To: PFC <lists@boutiquenumerique.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] OFFSET impact on Performance???
Date: Wed, 26 Jan 2005 13:35:27 -0500
Message-ID: <33c6269f0501261035bc87695@mail.gmail.com> (raw)
In-Reply-To: <opsk7nngxwth1vuj@musicbox>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local>
	<87brbiah0f.fsf@stark.xeocode.com>
	<opsk7nngxwth1vuj@musicbox>

The problem with this approach is TTFB (Time to first Byte).  The
initial query is very slow, but additional requests are fast.  In most
situations we do not want the user to have to wait a disproportionate
amount of time for the initial query.  If this is the first time using
the system this will be the impression that will stick with them.  I
guess we could experiment and see how much extra time creating a cache
table will take...

Alex Turner
NetEconomist


On Wed, 26 Jan 2005 13:58:18 +0100, PFC <lists@boutiquenumerique.com> wrote:
> 
> Supposing your searches display results which are rows coming from one
> specific table, you could create a cache table :
> 
> search_id       serial primary key
> index_n position of this result in the global result set
> result_id       id of the resulting row.
> 
> Then, making a search with 50k results would INSERT INTO cache ... SELECT
>  FROM search query, with a way to set the index_n column, which can be a
> temporary sequence...
> 
> Then to display your pages, SELECT from your table with index_n BETWEEN so
> and so, and join to the data table.
> 
> If you're worried that it might take up too much space : store an integer
> array of result_id instead of just a result_id ; this way you insert fewer
> rows and save on disk space. Generate it with a custom aggregate... then
> just grab a row from this table, it contains all the id's of the rows to
> display.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
>



view thread (50+ messages)  latest in thread

Message-ID: <33c6269f0501261035bc87695@mail.gmail.com>
Permalink:  ../33c6269f0501261035bc87695@mail.gmail.com/
Also on:    postgresql.org/message-id/33c6269f0501261035bc87695@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-performance@postgresql.org
  Cc: armtuk@gmail.com, lists@boutiquenumerique.com, gsstark@mit.edu, merlin.moncure@rcsonline.com, klodoma@ar-sd.net
  Subject: Re: [SQL] OFFSET impact on Performance???
  In-Reply-To: <33c6269f0501261035bc87695@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