pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PFC <lists@boutiquenumerique.com>
To: alex@neteconomist.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 22:24:34 +0100
Message-ID: <opsk8a28tcth1vuj@musicbox> (raw)
In-Reply-To: <33c6269f0501261035bc87695@mail.gmail.com>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75CB@Herge.rcsinc.local>
	<87brbiah0f.fsf@stark.xeocode.com>
	<opsk7nngxwth1vuj@musicbox>
	<33c6269f0501261035bc87695@mail.gmail.com>

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

	Do it on the second page then ;)

	Seriously :
	- If you want to display the result count and page count, you'll need to  
do the whole query anyway, so you might as well save the results.
	- inserting the result id's in a temp table one by one will be slow, but  
you can do this :

select array_accum(id) from temp group by id/20 limit 3;
                           array_accum
---------------------------------------------------------------
  {1,2,4,8,16,17,9,18,19,5,10,11,3,6,12,13,7,14,15}
  {32,33,34,35,36,37,38,39,20,21,22,23,24,25,26,27,28,29,30,31}
  {40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59}

	- a really big search of 131072 results :
create table cache (id serial primary key, value integer[]);
explain analyze insert into cache (value) select array_accum(id) from temp  
group by id/100;
  Subquery Scan "*SELECT*"  (cost=14382.02..17986.50 rows=131072 width=32)  
(actual time=961.746..1446.630 rows=1311 loops=1)
    ->  GroupAggregate  (cost=14382.02..16020.42 rows=131072 width=4)  
(actual time=961.607..1423.803 rows=1311 loops=1)
          ->  Sort  (cost=14382.02..14709.70 rows=131072 width=4) (actual  
time=961.181..1077.662 rows=131072 loops=1)
                Sort Key: (id / 100)
                ->  Seq Scan on "temp"  (cost=0.00..2216.40 rows=131072  
width=4) (actual time=0.032..291.652 rows=131072 loops=1)
  Total runtime: 1493.304 ms

	Note that the "SELECT..." part takes 1400 ms, and the INSERT part takes  
the rest, which is really small. It's the sort which takes most of the  
time, but you'll be doing it anyway to get your results in order, so it  
comes free to you. This will generate 1000 pages with 100 results on each.  
If your searches yield say 1000 results it'll be perfectly fine and can  
target times in the sub-100 ms for caching the results (not counting the  
total query time of course !)

	Using arrays is the key here, because inserting all the results as  
individual rows in the table is gonna be a whole lot slower !







view thread (50+ messages)  latest in thread

Message-ID: <opsk8a28tcth1vuj@musicbox>
Permalink:  ../opsk8a28tcth1vuj@musicbox/
Also on:    postgresql.org/message-id/opsk8a28tcth1vuj@musicbox

 · 

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: lists@boutiquenumerique.com, alex@neteconomist.com, gsstark@mit.edu, merlin.moncure@rcsonline.com, klodoma@ar-sd.net
  Subject: Re: [SQL] OFFSET impact on Performance???
  In-Reply-To: <opsk8a28tcth1vuj@musicbox>

* 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