Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 6F4DE6345B2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 23 Feb 2010 18:53:12 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 57056-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 23 Feb 2010 22:52:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outbound-mail-360.bluehost.com (outbound-mail-360.bluehost.com
	[66.147.249.254])
	by mail.postgresql.org (Postfix) with SMTP id 5D27A633D21
	for <pgsql-performance@postgresql.org>;
	Tue, 23 Feb 2010 18:53:01 -0400 (AST)
Received: (qmail 25456 invoked by uid 0); 23 Feb 2010 22:52:59 -0000
Received: from unknown (HELO box398.bluehost.com) (69.89.31.198)
	by oproxy2.bluehost.com with SMTP; 23 Feb 2010 22:52:59 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=negora.com;
	h=Received:Message-ID:Date:From:User-Agent:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding:X-Identified-User;
	b=hBzqxPgvhO6qTmYeABjsV+mgZJmDliWAIHEvkcvhOwfjkpjWjtK0fGx6Ab6qdTe8D3/zZlKiWHSSIY5h+ZGgQwa7E7IzyQYuQBb7M2fPHT+Ai9UMYzN5uIMbTJctmJTP;
Received: from 30.red-80-32-234.staticip.rima-tde.net ([80.32.234.30]
	helo=[172.16.0.2]) by box398.bluehost.com with esmtpa (Exim 4.69)
	(envelope-from <negora@negora.com>)
	id 1Nk3ch-0002MN-BL; Tue, 23 Feb 2010 15:52:59 -0700
Message-ID: <4B845C48.2030904@negora.com>
Date: Tue, 23 Feb 2010 23:52:56 +0100
From: negora <negora@negora.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Internal operations when the planner makes a	 hash
 join.
References: <4B83E8C5.2050902@negora.com>
	<4B83A03E020000250002F4FD@gw.wicourts.gov>
	<4B8404CC.4060607@negora.com>
	<20100223165339.GF3672@alvh.no-ip.org>
	<0B256120-1CC5-4B5F-B257-CCF72DE9DE2F@richrelevance.com>
	<4B8449A4.3090109@negora.com>
	<4B83F811020000250002F579@gw.wicourts.gov>
In-Reply-To: <4B83F811020000250002F579@gw.wicourts.gov>
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Identified-User: {2410:box398.bluehost.com:negoraco:negora.com} {sentby:smtp
	auth 80.32.234.30 authed with negora+negora.com}
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.338 tagged_above=-10 required=5 tests=AWL=0.488,
	BAYES_00=-2.599, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457,
	SARE_MILLIONSOF=0.315
X-Spam-Level: 
X-Archive-Number: 201002/335
X-Sequence-Number: 37756

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=US-ASCII" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Verdana">Hello Kevin. I'm going to take and apply your
advices, certainly. No more "crazy" PL/PgSQLs then. I was worried
because of the possibility that repetition of fields caused some kind
of memory saturation. But I guess that PostgreSQL takes care of that
fact properly. I even might return the entire result to my external
Java application (I was using a similar approach on it too). I just
hope that the speed of that single SQL compensates the transfer of such
a big mass of data between PostgreSQL and Java in terms of delay.
Thanks ;) .<br>
<br>
<br>
</font><br>
Kevin Grittner wrote:
<blockquote cite="mid:4B83F811020000250002F579@gw.wicourts.gov"
 type="cite">
  <pre wrap="">negora <a class="moz-txt-link-rfc2396E" href="mailto:negora@negora.com">&lt;negora@negora.com&gt;</a> wrote:
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">The origin of my doubt resides in the fact that I need to do a
joint between 3 HUGE tables (millions of registries) and do
certain operations with the retrieved information. I was deciding
whether to use one SELECT with 3 JOINs, as I've been doing since
the beginning, or build a PL/PgSQL function based on 3 nested "FOR
... IN SELECT ... LOOP" structures which tried to minimize the
subsequent table searches storing intermediate useful data in
arrays
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
It's almost always faster (and less error prone) to write one SELECT
statement declaring what you want than to try to do better by
navigating individual rows procedurally.  I would *strongly*
recommend you write it with the JOINs and then post here if you have
any concerns about the performance.  In general, try to *declare*
what you want, and let the PostgreSQL planner sort out the best way
to navigate the tables to produce what you want.  If you hit some
particular weakness in the planner, you many need to coerce it, but
certainly you should not *start* with that.
 
-Kevin

  </pre>
</blockquote>
</body>
</html>