Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 281A29F98C5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  3 Nov 2007 20:04:00 -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 98381-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  3 Nov 2007 20:03:44 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from fk-out-0910.google.com (fk-out-0910.google.com
 [209.85.128.187])
	by postgresql.org (Postfix) with ESMTP id ECD329F98C6
	for <pgsql-performance@postgresql.org>;
	Sat,  3 Nov 2007 20:03:49 -0300 (ADT)
Received: by fk-out-0910.google.com with SMTP id b27so1199913fka
	for <pgsql-performance@postgresql.org>;
	Sat, 03 Nov 2007 16:03:48 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	bh=85kUNUSk2PLscGHavd6Ozys4RT7w+YhCLi/a8xDOb18=;
	b=kLpIV6t43+hmazidyG3UHrD7fUIXm+3vwPm8Zj3TBJf5JyqaBeqaxp7n14VBeVIxZcsNqjg19SWfdbR+sDDkbBYlQFg9cCVD/rcKaDB/LuaXGK0NxFO4MnxcVKrERe7qxeHmmsYUodczJQwX/2MduqlKH0N+V/vtoOmIf1RtkKQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=MmO8xEsAynyibPqugzhznhzJ+mQi/c4PWu/TDreusRuRshzr9O1PSB2LE71njRpojSxwOTuJ0YXicZpIglTY9Jl1tCCGVePe+6uErGFQE4TOpQR34odgB4Akvr066K0nOdMyvIMsSNrXCJgWFObX2vmkxx1exi8i0Wv/oYGVLnE=
Received: by 10.82.107.15 with SMTP id f15mr6486972buc.1194131027085;
	Sat, 03 Nov 2007 16:03:47 -0700 (PDT)
Received: from ?192.168.1.33? ( [86.27.95.56])
	by mx.google.com with ESMTPS id f3sm4729497nfh.2007.11.03.16.03.42
	(version=SSLv3 cipher=RC4-MD5); Sat, 03 Nov 2007 16:03:45 -0700 (PDT)
Message-ID: <472CFE4B.5060606@enterprisedb.com>
Date: Sat, 03 Nov 2007 23:03:39 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: Jeff Larsen <jlar310@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Union within View vs.Union of Views
References: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com>
In-Reply-To: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/40
X-Sequence-Number: 27455

Jeff Larsen wrote:
> Performance on
> 
> SELECT * from VIEW_X WHERE <conditions>;
> 
> was absolutely terrible. But performance on
> 
> SELECT * from VIEW_A WHERE <conditions>
> UNION ALL
> SELECT * from VIEW_B WHERE <conditions>
> UNION ALL
> SELECT * from VIEW_C WHERE <conditions>;
> 
> was nice and speedy, perhaps 100 times faster than the first.
> 
> If it's possible to consider this abstractly, is there any particular
> reason why there is such a vast difference in performance? I would
> guess that is has something to do with how the WHERE conditions are
> applied to a view composed of a UNION of queries. Perhaps this is an
> opportunity for improvement in the code. In the first case, it's as if
> the server is doing the union on all rows (over 10 million altogether
> in my case) without filtering, then applying the conditions to the
> result. Maybe there is no better way.

That's surprising. The planner knows how to push down WHERE conditions 
to parts of a UNION ALL, and should be able to generate the same plan in 
both cases. Maybe it's just estimating the costs differently? Did you 
copy-paste all the conditions in the single WHERE clause of the slow 
query to all the three WHERE clauses on the separate views? Even if some 
of the clauses are not applicable, they might still affect the cost 
estimates and lead to a worse plan.

> I can post query plans if anyone is interested. I haven't really
> learned how to make sense out of them myself yet.

Yes, please. Please post the SQL and schema as well if possible.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com