Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 0D4E39FB814 for ; Sat, 27 Oct 2007 18:28:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 67162-03 for ; Sat, 27 Oct 2007 18:28:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outmail136007.authsmtp.co.uk (outmail136007.authsmtp.co.uk [62.13.136.7]) by postgresql.org (Postfix) with ESMTP id 1E2169FB803 for ; Sat, 27 Oct 2007 18:28:25 -0300 (ADT) Received: from outmail128184.authsmtp.net (outmail128184.authsmtp.net [62.13.128.184]) by punt3.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9RLSKuH082990; Sat, 27 Oct 2007 22:28:20 +0100 (BST) Received: from [192.168.0.3] (62-3-250-177.dyn.gotadsl.co.uk [62.3.250.177]) (authenticated bits=0) by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l9RLSHdm097890; Sat, 27 Oct 2007 22:28:17 +0100 (BST) Subject: Re: Speed difference between select ... union select ... and select from partitioned_table From: Simon Riggs 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: Sat, 27 Oct 2007 22:26:49 +0100 Message-Id: <1193520409.4242.604.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Server-Quench: 88c1d332-84d3-11dc-b86a-001185d377ca X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadBtFagJbYlpF HGQLW1NEUl97W2t/ agIfawdDYVRMWgV0 UU9OXVBXCwUmXRVj en1mBkRyfgJOcX4+ ZENiVz5TVBd7IUV+ QFNcETkFeGZhPDMC AkheJh5UcAJLdhoR aVd4ByJDAzANdhEA MjUdGAoUCH1kMClY TkkBLFwcTFwGGDUx DxoPASoiEEtNXCg3 Ihc6YhhaWg4MNEQ1 NxM6VFYVOANaTEsY fSBEBiMRPFQFSiE3 RQ1AWktWCDRHXSZA AxouLxN/HjVYXicw X-Authentic-SMTP: 61633235383639.squirrel.dmpriest.net.uk:752/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.236 tagged_above=0 required=5 tests=AWL=-0.235, BAYES_50=0.001, SARE_MILLIONSOF=0.315, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 200710/324 X-Sequence-Number: 27346 On Fri, 2007-10-26 at 16:37 -0400, Pablo Alcaraz wrote: > 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. > > My question is: why? :) The two queries are equivalent but they have different execution plans. The UNION query has explicit GROUP BY operations within it. We do not currently perform a push-down operation onto the individual partitions. This results in more data copying as well as requiring a single very large sort, rather than lots of small ones. That is probably enough to allow it to perform the sort in memory rather than on-disk, thus allowing a considerable speed-up. This is on my list of requirements for further partitioning improvements in 8.4 or beyond. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com