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