Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 67CFF6336E0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  5 Nov 2009 09:43:07 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 46223-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  5 Nov 2009 13:42:59 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com
	[209.85.218.213])
	by mail.postgresql.org (Postfix) with ESMTP id DD0C0634F34
	for <pgsql-performance@postgresql.org>;
	Thu,  5 Nov 2009 09:42:58 -0400 (AST)
Received: by bwz5 with SMTP id 5so876669bwz.3
	for <pgsql-performance@postgresql.org>;
	Thu, 05 Nov 2009 05:42:56 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=jcJKSRjoQyfInQY9SyjsIwcjh0YJ4CikrjloS7ZqePM=;
	b=A4+M83I+s/NvK1Ioy4dae4QpSTR6ubSMsOyIYqXx3uaZk/dQKLQYmULhv+TP/VnE0K
	PJxlV98e9rAtjoPH+SvJnqmM1SzoMUsJFO0r4IG2Uvb3smuKuvg0kSq24JZTYNbyQSHq
	6LgiF7R0qBFEhMN1wFQKnj9s8dTYkQQYrfyZc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=aUP8wHsjDkrobmVD1x5qkYsvcZC1kQ2b9TxyZNUJEv3qmxJ0jHrsX1sniVqOqr6BHa
	I2g5T9s3oHc73rmzaLz1is8D/hreqrGMDlMqosLXvRIyoyKKl0cHZ1/Tna3zuYtRMWvw
	25UENSf/OH5omKajxTolRe+C2wuNPKWVgObNs=
MIME-Version: 1.0
Received: by 10.204.36.210 with SMTP id u18mr3105303bkd.19.1257428576653;
 Thu,
	05 Nov 2009 05:42:56 -0800 (PST)
In-Reply-To: <alpine.DEB.2.00.0911051335410.19472@aragorn.flymine.org>
References: <abf9211d0911050136g48739f0ft53b63f96cddf706@mail.gmail.com>
	<2f4958ff0911050138n3f246b7ck6182cb702ac48e91@mail.gmail.com>
	<407d949e0911050332n5ef355c1y156dcd26aa28d222@mail.gmail.com>
	<abf9211d0911050520t72a5d6dfv877ced07077b11@mail.gmail.com>
	<2f4958ff0911050525w678925uc8f63fe966951ef5@mail.gmail.com>
	<alpine.DEB.2.00.0911051335410.19472@aragorn.flymine.org>
Date: Thu, 5 Nov 2009 13:42:56 +0000
Message-ID: <2f4958ff0911050542m479791c6lb0772f23a474cd37@mail.gmail.com>
Subject: Re: Running some query in low priority
From: =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: S Arvind <arvindwill@gmail.com>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=00032555563ea452b104779fe7a9
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200911/69
X-Sequence-Number: 36237

--00032555563ea452b104779fe7a9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2009/11/5 Matthew Wakeling <matthew@flymine.org>

> On Thu, 5 Nov 2009, Grzegorz Ja=C5=9Bkiewicz wrote:
>
>> If it is an insert of some sort, than divide it up. If it is a query tha=
t
>> runs over data,
>> use limits, and do it in small batches. Overall, divide in conquer
>> approach works in
>> these scenarios.
>>
>
> Unfortunately, dividing the work up can cause a much greater load, which
> would make things worse. If you are inserting in smaller chunks and
> committing more frequently that can reduce performance. If you split up
> queries with limit and offset, that will just multiply the number of time=
s
> the query has to be run. Each time, the query will be evaluated, the firs=
t
> <offset> rows thrown away, and the next <limit> rows returned, which will
> waste a huge amount of time.
>
> If you are inserting data, then use a COPY from stdin, and then you can
> throttle the data stream. When you are querying, declare a cursor, and fe=
tch
> from it at a throttled rate.
>

as with everything, you have to find the right balance. I think he is
looking for low impact, not speed. So he has to trade one for another. Find
a small enough batch size, but not too small, cos like you said - things
will have too much impact otherwise.


--=20
GJ

--00032555563ea452b104779fe7a9
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">2009/11/5 Matthew Wakeling <span dir=3D"=
ltr">&lt;<a href=3D"mailto:matthew@flymine.org">matthew@flymine.org</a>&gt;=
</span><br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px soli=
d rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class=3D"im">On Thu, 5 Nov 2009, Grzegorz Ja=C5=9Bkiewicz wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
If it is an insert of some sort, than divide it up. If it is a query that r=
uns over data,<br>
use limits, and do it in small batches. Overall, divide in conquer approach=
 works in<br>
these scenarios.<br>
</blockquote>
<br></div>
Unfortunately, dividing the work up can cause a much greater load, which wo=
uld make things worse. If you are inserting in smaller chunks and committin=
g more frequently that can reduce performance. If you split up queries with=
 limit and offset, that will just multiply the number of times the query ha=
s to be run. Each time, the query will be evaluated, the first &lt;offset&g=
t; rows thrown away, and the next &lt;limit&gt; rows returned, which will w=
aste a huge amount of time.<br>

<br>
If you are inserting data, then use a COPY from stdin, and then you can thr=
ottle the data stream. When you are querying, declare a cursor, and fetch f=
rom it at a throttled rate.<br></blockquote><div><br>as with everything, yo=
u have to find the right balance. I think he is looking for low impact, not=
 speed. So he has to trade one for another. Find a small enough batch size,=
 but not too small, cos like you said - things will have too much impact ot=
herwise.<br>
<br></div></div><br>-- <br>GJ<br>

--00032555563ea452b104779fe7a9--