Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BEF579FB3C4 for ; Sat, 27 Oct 2007 10:17:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 88939-04 for ; Sat, 27 Oct 2007 10:17:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 61F109FB52A for ; Sat, 27 Oct 2007 10:15:07 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9RDF2ED014888; Sat, 27 Oct 2007 09:15:02 -0400 (EDT) To: Pablo Alcaraz cc: pgsql-performance@postgresql.org Subject: Re: Speed difference between select ... union select ... and select from partitioned_table In-reply-to: <4722C92D.8050501@laotraesquina.com.ar> References: <47225014.5040101@laotraesquina.com.ar> <1193432821.7624.83.camel@dogma.ljc.laika.com> <4722C92D.8050501@laotraesquina.com.ar> Comments: In-reply-to Pablo Alcaraz message dated "Sat, 27 Oct 2007 01:14:21 -0400" Date: Sat, 27 Oct 2007 09:15:02 -0400 Message-ID: <14887.1193490902@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/321 X-Sequence-Number: 27343 Pablo Alcaraz writes: > 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... regards, tom lane