From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: queries with lots of UNIONed relations
Date: Thu, 13 Jan 2011 18:05:04 -0500
Message-ID: <18301.1294959904@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTikH4eyp5JwJpUEkfOX4u2hOemGDCoXPEf01dkAv@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>
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jan 13, 2011 at 5:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I don't believe there is any case where hashing each individual relation
>> is a win compared to hashing them all together. If the optimizer were
>> smart enough to be considering the situation as a whole, it would always
>> do the latter.
> You might be right, but I'm not sure. Suppose that there are 100
> inheritance children, and each has 10,000 distinct values, but none of
> them are common between the tables. In that situation, de-duplicating
> each individual table requires a hash table that can hold 10,000
> entries. But deduplicating everything at once requires a hash table
> that can hold 1,000,000 entries.
> Or am I all wet?
If you have enough memory to de-dup them individually, you surely have
enough to de-dup all at once. It is not possible for a single hashtable
to have worse memory consumption than N hashtables followed by a union
hashtable, and in fact if there are no common values then the latter eats
twice as much space because every value appears twice in two different
hashtables.
regards, tom lane
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, robertmhaas@gmail.com, jnelson+pgsql@jamponi.net
Subject: Re: queries with lots of UNIONed relations
In-Reply-To: <18301.1294959904@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