pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Replication Ideas
Date: Mon, 25 Aug 2003 18:28:09 -0400
Message-ID: <m3brud9xty.fsf@chvatal.cbbrowne.com> (raw)
References: <3F483EBD.3020108@travelamericas.com>
	<1061705587.3733.452.camel@haggis>
	<3F4A420E.6090604@travelamericas.com>
	<20030825182441.GB9597@dcc.uchile.cl>
	<3F4A5724.3060002@travelamericas.com>
	<11627.1061845759@sss.pgh.pa.us>
	<3F4A8A7D.30308@travelamericas.com>

A long time ago, in a galaxy far, far away, "Bupp Phillips" <hello@noname.com> wrote:
>I have a table that has 103,000 records in it (record size is about
>953 bytes) and when I do a select all (select * from <table>) it takes
>a whopping 30 secs for the data to return!!
 
>SQLServer on the other hand takes 6 secs, but you can also use what is
>called a firehose cursor, which will return the data in < 1 sec. 
 
>I have done everything that I know how to speed this up, does anyone
>have any advise?

Have you VACUUMed the table?  30 seconds to start getting data back
from such a query _seems_ a liittle high.

It would be quite a reasonable idea to open up a CURSOR and request
the data in more byte-sized pieces so that the result set wouldn't
forcibly bloat in any one spot.

You start by submitting the cursor definition, inside a transaction:
  begin transaction;
  declare cursor my_fire_hose for select * from <table>;

You then iterate over the following, which fetches 1000 rows at a time:
  fetch forward 1000 in my_fire_hose;

That should cut down the time it takes to start getting records to
near enough to zero...
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/lisp.html
"Microsoft is sort of a mixture between the Borg and the
Ferengi. Combine the Borg marketing with Ferengi networking..."
-- Andre Beck in dcouln



view thread (22+ messages)  latest in thread

Message-ID: <m3brud9xty.fsf@chvatal.cbbrowne.com>
Permalink:  ../m3brud9xty.fsf@chvatal.cbbrowne.com/
Also on:    postgresql.org/message-id/m3brud9xty.fsf@chvatal.cbbrowne.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Replication Ideas
  In-Reply-To: <m3brud9xty.fsf@chvatal.cbbrowne.com>
  References: <3F483EBD.3020108@travelamericas.com> <1061705587.3733.452.camel@haggis> <3F4A420E.6090604@travelamericas.com> <20030825182441.GB9597@dcc.uchile.cl> <3F4A5724.3060002@travelamericas.com> <11627.1061845759@sss.pgh.pa.us> <3F4A8A7D.30308@travelamericas.com> <m3brud9xty.fsf@chvatal.cbbrowne.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