Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 100499FA18F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 27 Oct 2007 19:31:31 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 48515-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 27 Oct 2007 19:30:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ar21.toservers.com (ar21.toservers.com [200.80.42.121])
	by postgresql.org (Postfix) with ESMTP id E783B9FA15F
	for <pgsql-performance@postgresql.org>;
	Sat, 27 Oct 2007 19:31:26 -0300 (ADT)
Received: from ar21.toservers.com (localhost [127.0.0.1])
	by ar21.toservers.com (Postfix) with ESMTP id C5E90414154
	for <pgsql-performance@postgresql.org>;
	Sat, 27 Oct 2007 19:31:24 -0300 (ART)
Received: by ar21.toservers.com (Postfix, from userid 1551)
	id BD2774142A6; Sat, 27 Oct 2007 19:31:24 -0300 (ART)
Received: from [192.168.1.4] (ar21.toservers.com [200.80.42.121])
	by ar21.toservers.com (Postfix) with SMTP id 850B0414154
	for <pgsql-performance@postgresql.org>;
	Sat, 27 Oct 2007 19:31:23 -0300 (ART)
Message-ID: <4723BC36.8000300@laotraesquina.com.ar>
Date: Sat, 27 Oct 2007 18:31:18 -0400
From: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Speed difference between select ... union select ...
	and select from partitioned_table
References: <47225014.5040101@laotraesquina.com.ar>
	<1193432821.7624.83.camel@dogma.ljc.laika.com>
	<4722C92D.8050501@laotraesquina.com.ar>
In-Reply-To: <4722C92D.8050501@laotraesquina.com.ar>
Content-Type: multipart/alternative;
	boundary="------------090602000704060407020801"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.688 tagged_above=0 required=5 tests=AWL=1.530,
	BAYES_50=0.001, HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.156
X-Spam-Level: *
X-Archive-Number: 200710/327
X-Sequence-Number: 27349

This is a multi-part message in MIME format.
--------------090602000704060407020801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Pablo Alcaraz wrote:
> These are the EXPLAIN ANALIZE:
>

If you raise work_mem enough to let the second query use a hash
aggregate (probably a few MB would do it), I think it'll be about
the same speed as the first one.

The reason it's not picking that on its own is the overestimate
of the number of resulting groups.  This is because
get_variable_numdistinct is not smart about append relations.
We should try to fix that sometime...



I re run the partitioned-query. it completed in 15996 seconds. It 
builded a BIG temp file:

[root@igor xxx]# ls -lh pgsql-data/data/16386/pgsql_tmp/
total 2.2G
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.0
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.1
-rw------- 1 postgres postgres 175M Oct 27 15:35 pgsql_tmp7004.2

work_mem=1Mb. How much do I need to raise work_mem variable? 2.2G?

Regards

Pablo

--------------090602000704060407020801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Pablo Alcaraz wrote:
<blockquote cite="mid:4722C92D.8050501@laotraesquina.com.ar" type="cite">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
These are the EXPLAIN ANALIZE:<br>
  <br>
</blockquote>
<br>
<pre><font><font face="Arial, Helvetica, sans-serif">If you raise work_mem enough to let the second query use a hash
aggregate (probably a few MB would do it), I think it'll be about
the same speed as the first one.

The reason it's not picking that on its own is the overestimate
of the number of resulting groups.  This is because
get_variable_numdistinct is not smart about append relations.
We should try to fix that sometime...</font></font></pre>
<br>
<br>
I re run the partitioned-query. it completed in 15996 seconds. It
builded a BIG temp file:<br>
<br>
[root@igor xxx]# ls -lh pgsql-data/data/16386/pgsql_tmp/<br>
total 2.2G<br>
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.0<br>
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.1<br>
-rw------- 1 postgres postgres 175M Oct 27 15:35 pgsql_tmp7004.2<br>
<br>
work_mem=1Mb. How much do I need to raise work_mem variable? 2.2G?<br>
<br>
Regards<br>
<br>
Pablo<br>
</body>
</html>

--------------090602000704060407020801--