Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EADF71337B3E for ; Mon, 17 Jan 2011 17:33:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 08783-07 for ; Mon, 17 Jan 2011 21:33:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 63D69133616B for ; Mon, 17 Jan 2011 17:33:48 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0HLXjbw021548; Mon, 17 Jan 2011 16:33:45 -0500 (EST) To: Pavel Stehule cc: Kevin Grittner , zotov@oe-it.ru, pgsql-performance@postgresql.org Subject: Re: Bad plan when join on function In-reply-to: References: <4D34576C02000025000396CB@gw.wicourts.gov> Comments: In-reply-to Pavel Stehule message dated "Mon, 17 Jan 2011 22:24:33 +0100" Date: Mon, 17 Jan 2011 16:33:44 -0500 Message-ID: <21547.1295300024@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/141 X-Sequence-Number: 41928 Pavel Stehule writes: > it should to work without functional index - but not sure about effectivity As long as the function is VOLATILE, the planner can't use any intelligent query plan. Merge or hash join both require at least stable join keys. regards, tom lane