Re: queries with lots of UNIONed relations

On Thu, Jan 13, 2011 at 2:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@jamponi.net> writes:
>> Your comment regarding "each individual de-duplication looked like it
>> would fit in work_mem" doesn't really make sense, exactly. Maybe I'm
>> misunderstanding you.
>
> Yeah.  What I was suggesting was to NOT add the DISTINCT's, but instead
> raise work_mem high enough so you get just one HashAggregation step at
> the top level.  (Although I think this only works in 8.4 and up.)
> That should be faster than two levels of de-duplication.

Gave it a try -- performance either way doesn't seem to change -
although the final set that has to undergo de-duplication is rather
larger (WITHOUT DISTINCT) so I still run the risk of not getting Hash
Aggregation.

Since having the DISTINCT doesn't seem to hurt, and it avoids
(potential) significant pain, I'll keep it.

I still think that having UNION do de-duplication of each contributory
relation is a beneficial thing to consider -- especially if postgresql
thinks the uniqueness is not very high.

Thanks!

--
Jon