To: Jon Nelson <jnelson+pgsql@jamponi.net>
cc: Robert Haas <robertmhaas@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: queries with lots of UNIONed relations 
In-reply-to: <AANLkTinubA5_CFAdERCyzDy1H3v409pyVTzdvPjTaPpr@mail.gmail.com> 
References: <AANLkTiktjfaR9_sDU+F3UZhAQn7qrBktXE1EiOX=inzQ@mail.gmail.com>
	<19302.1294938802@sss.pgh.pa.us>
	<AANLkTikM9=JXPwYpAbfQ5KZgenWBCn5_J2=W6MRhq0U0@mail.gmail.com>
	<4879.1294949144@sss.pgh.pa.us>
	<AANLkTimx00ADoFDbOyFixLOSy4ypKjhQrHnTzanepbJD@mail.gmail.com>
	<AANLkTikYEX25b4E_AoZk=WWspv2OC7ZKct2VeZb1NCMj@mail.gmail.com>
	<11563.1294957582@sss.pgh.pa.us>
	<AANLkTikH4eyp5JwJpUEkfOX4u2hOemGDCoXPEf01dkAv@mail.gmail.com>
	<18301.1294959904@sss.pgh.pa.us>
	<AANLkTinubA5_CFAdERCyzDy1H3v409pyVTzdvPjTaPpr@mail.gmail.com>
Comments: In-reply-to Jon Nelson <jnelson+pgsql@jamponi.net>
	message dated "Thu, 13 Jan 2011 17:14:04 -0600"
Date: Thu, 13 Jan 2011 19:10:27 -0500
Message-ID: <20541.1294963827@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> On Thu, Jan 13, 2011 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you have enough memory to de-dup them individually, you surely have
>> enough to de-dup all at once.

> If everything were available up-front, sure.
> However, and please correct me if I'm wrong, but doesn't postgresql
> work in a fairly linear fashion, moving from table to table performing
> a series of operations on each?

Doing a single sort+uniq works like that.  But the alternate plan you
are proposing we should consider involves building all the lower
hashtables, and then reading from them to fill the upper hashtable.
Max memory consumption *is* worst case here.  Remember HashAggregate
is incapable of swapping to disk (and if it did, you wouldn't be nearly
as pleased with its performance).

			regards, tom lane