Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0B9889F95A6 for ; Sun, 4 Nov 2007 06:11:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 29207-01 for ; Sun, 4 Nov 2007 06:10:54 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.184]) by postgresql.org (Postfix) with ESMTP id 71A209F95A1 for ; Sun, 4 Nov 2007 06:11:08 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id l15so1045257rvb for ; Sun, 04 Nov 2007 02:11:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=mNVdNFbgcJG80fLb2w8LW+/NuK6zalU8sk6PIM/KGXQ=; b=S0xisjw5wiJ6ns9LwTkEO9ItebIHQRLPSmKAtVqKlNILR2515SCkJSoPu6uV99ID/XN69b/uFpeAHt7oLdWGy7Y0FY3Fes7JGtAs4VQ0KfKuRCFZCl6bGr//ZPZPar5hCL8fuZ29MKTr14GMSDkxDiaxACP7FAMRvgz1Q2Dr35w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DKJYj/AASt80vkwFxk0ofqLIbxFK1uQIJTraMQpMgi4zBO/FcPrVQtakqKN2LJUhIBS933NqCsDa6lawT0y/ENEJgHX4sbnjYXoNystsH2q32MQFH7C3PLSEAWyLXV73+ybqohgNS6gJ3GmORC/vegc8wPabW/KZURDojMSsn0Y= Received: by 10.142.128.6 with SMTP id a6mr824536wfd.1194171062699; Sun, 04 Nov 2007 02:11:02 -0800 (PST) Received: by 10.143.35.10 with HTTP; Sun, 4 Nov 2007 02:11:02 -0800 (PST) Message-ID: Date: Sun, 4 Nov 2007 04:11:02 -0600 From: "Jeff Larsen" To: pgsql-performance@postgresql.org Subject: Re: Union within View vs.Union of Views In-Reply-To: <3606.1194141646@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <472CFE4B.5060606@enterprisedb.com> <3248.1194140315@sss.pgh.pa.us> <3606.1194141646@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/45 X-Sequence-Number: 27460 On 11/3/07, Tom Lane wrote: > "Jeff Larsen" writes: > > My case probably fits the 'special case' description. Not all the > > columns in each subquery matched up, so there were NULL::text > > placeholders in some spots in the SELECT. In the case where > > performance got bad, one of those columns was included in the > > application's WHERE clause. > > Please see if explicitly casting the nulls to the same datatype as the > other items they're unioned with makes it go fast. It sounds like you > are hitting the "no type coercions" restriction. Sure enough, explicitly casting to exactly the same type for each column did the trick. In fact the union within the view now has a slight edge over the union of views. Thanks, Jeff