Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B327D9F9BB5 for ; Fri, 26 Oct 2007 18:07:10 -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 82140-07 for ; Fri, 26 Oct 2007 18:07:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id 5CC189F9BA9 for ; Fri, 26 Oct 2007 18:07:05 -0300 (ADT) Received: from cragnux.laika.com ([209.162.219.253]:53422 helo=dogma.ljc.laika.com) by serv01.siteground137.com with esmtpa (Exim 4.68) (envelope-from ) id 1IlWOT-00017E-F9; Fri, 26 Oct 2007 16:07:01 -0500 Subject: Re: Speed difference between select ... union select ... and select from partitioned_table From: Jeff Davis To: Pablo Alcaraz Cc: pgsql-performance@postgresql.org In-Reply-To: <47225014.5040101@laotraesquina.com.ar> References: <47225014.5040101@laotraesquina.com.ar> Content-Type: text/plain Date: Fri, 26 Oct 2007 14:07:01 -0700 Message-Id: <1193432821.7624.83.camel@dogma.ljc.laika.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground137.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - j-davis.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/308 X-Sequence-Number: 27330 On Fri, 2007-10-26 at 16:37 -0400, Pablo Alcaraz wrote: > Hi List! > > I executed 2 equivalents queries. The first one uses a union structure. > The second uses a partitioned table. The tables are the same with 30 > millions of rows each one and the returned rows are the same. > > But the union query perform faster than the partitioned query. > I think you mean to use UNION ALL here. UNION forces a DISTINCT, which results in a sort operation. What surprises me is that the UNION is actually faster than the partitioning using inheritance. I suspect it has something to do with the GROUP BYs, but we won't know until you post EXPLAIN ANALYZE results. Regards, Jeff Davis