pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: queries with lots of UNIONed relations
Date: Thu, 13 Jan 2011 19:10:27 -0500
Message-ID: <20541.1294963827@sss.pgh.pa.us> (raw)
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>

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



view thread (22+ messages)  latest in thread

Message-ID: <20541.1294963827@sss.pgh.pa.us>
Permalink:  ../20541.1294963827@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/20541.1294963827@sss.pgh.pa.us

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jnelson+pgsql@jamponi.net, robertmhaas@gmail.com
  Subject: Re: queries with lots of UNIONed relations
  In-Reply-To: <20541.1294963827@sss.pgh.pa.us>

* 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