Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 276462E003A for ; Mon, 7 Apr 2008 12:46:42 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81341-07 for ; Mon, 7 Apr 2008 12:46:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from hs-out-0708.google.com (hs-out-0708.google.com [64.233.178.246]) by developer.postgresql.org (Postfix) with ESMTP id E5F3C2E0038 for ; Mon, 7 Apr 2008 12:46:38 -0300 (ADT) Received: by hs-out-0708.google.com with SMTP id x43so7900312hsb.3 for ; Mon, 07 Apr 2008 08:46:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Gu4Xh1bs4fDwScXWS4O2zx61ZDwTC6tGGH10CqGKpPI=; b=mCg5EkAmeHE7vBmwBZ7NA7tAy0RJ7kl+kXKePBApb7ORxunlFxe92xfH0u4RLVAYseny8DDJbxtQCi9+tTfKXYrUI2uhm99Kc7F0TIrTnPZexSlvAurN1YeEFRwYIqaEMDLlZkHJp+rETHZhwdtXlKx1SRRIDLKJe3wTJSaRy9w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=b+cE6i7ecdCjZeL6Gig7Lr1wrWZhORtJKyEb3AnMrWugJux8Q7LvlISKHbTcSaieXv0iehAj0x+h3klAus0hRPWgbtRMfUBR5SsYMfzk7Mb/7ZmFe7TV6yAZiAw4H+Em5411do+CdWVlCOXpHonPjUF5Yz1DC93YOLuuMHQTdlE= Received: by 10.100.33.4 with SMTP id g4mr476108ang.129.1207583198148; Mon, 07 Apr 2008 08:46:38 -0700 (PDT) Received: by 10.100.194.10 with HTTP; Mon, 7 Apr 2008 08:46:38 -0700 (PDT) Message-ID: <3bda20f60804070846w6e89fa2bg3676ef0d00d14fec@mail.gmail.com> Date: Mon, 7 Apr 2008 09:46:38 -0600 From: "Matt Klinker" To: "Tom Lane" Subject: Re: [SOLVED] Query plan excluding index on view Cc: pgsql-performance@postgresql.org In-Reply-To: <28880.1207369243@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_808_22241409.1207583198156" References: <3bda20f60804031631j177b42e2u82b5033fd23c2293@mail.gmail.com> <28177.1207266621@sss.pgh.pa.us> <3bda20f60804031654q7991de08m1254964511632acf@mail.gmail.com> <29828.1207271948@sss.pgh.pa.us> <3bda20f60804032158u4f6765e3t8b0debad3e7f5ceb@mail.gmail.com> <4767.1207288167@sss.pgh.pa.us> <3bda20f60804040726m15e1b9caqad3883cba960bab0@mail.gmail.com> <28880.1207369243@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/55 X-Sequence-Number: 29548 ------=_Part_808_22241409.1207583198156 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Removing the constants definitely did take care of the issue on 8.3 (still same query plan on 8.1). Thanks for your help in getting this resolved, and sorry again for not including all relevant information on my initial request On Fri, Apr 4, 2008 at 10:20 PM, Tom Lane wrote: > "Matt Klinker" writes: > > --Joined View: > > CREATE OR REPLACE VIEW directory_listing AS > > SELECT school.id, school.name, school.description, 119075291 AS > > listing_type_fid > > FROM school > > UNION ALL > > SELECT company.id, company.name, company.description, 119074833 AS > > listing_type_fid > > FROM company; > > Ah, there's the problem :-(. Can you get rid of the constants here? > The planner's currently not smart about UNION ALL subqueries unless > their SELECT lists contain just simple column references. > > (Yes, fixing that is on the todo list, but don't hold your breath... > it'll be 8.4 material at the earliest.) > > regards, tom lane > ------=_Part_808_22241409.1207583198156 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Removing the constants definitely did take care of the issue on 8.3 (still same query plan on 8.1).  Thanks for your help in getting this resolved, and sorry again for not including all relevant information on my initial request

On Fri, Apr 4, 2008 at 10:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Matt Klinker" <mklinker@gmail.com> writes:
> --Joined View:
> CREATE OR REPLACE VIEW directory_listing AS
>  SELECT school.id, school.name, school.description, 119075291 AS
> listing_type_fid
>    FROM school
> UNION ALL
>  SELECT company.id, company.name, company.description, 119074833 AS
> listing_type_fid
>    FROM company;

Ah, there's the problem :-(.  Can you get rid of the constants here?
The planner's currently not smart about UNION ALL subqueries unless
their SELECT lists contain just simple column references.

(Yes, fixing that is on the todo list, but don't hold your breath...
it'll be 8.4 material at the earliest.)

                       regards, tom lane

------=_Part_808_22241409.1207583198156--