Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 6FD001FA2A4C for ; Thu, 26 Jul 2012 12:53:15 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SuQNC-0003o6-PW for pgsql-sql@postgresql.org; Thu, 26 Jul 2012 15:53:14 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6QFpe92022989; Thu, 26 Jul 2012 11:51:40 -0400 (EDT) From: Tom Lane To: Russell Keane cc: "pgsql-sql@postgresql.org" Subject: Re: FW: view derived from view doesn't use indexes In-reply-to: <8D0E5D045E36124A8F1DDDB463D548557CEF4D2D44@mxsvr1.is.inps.co.uk> References: <8D0E5D045E36124A8F1DDDB463D548557CEF4D2D44@mxsvr1.is.inps.co.uk> Comments: In-reply-to Russell Keane message dated "Thu, 26 Jul 2012 15:59:32 +0100" Date: Thu, 26 Jul 2012 11:51:40 -0400 Message-ID: <22988.1343317900@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/31 X-Sequence-Number: 36768 Russell Keane writes: > Using PG 9.0 and given the following definitions: > CREATE OR REPLACE FUNCTION status_to_flag(status character) > RETURNS integer AS > $BODY$ > ... > $BODY$ > LANGUAGE plpgsql > CREATE OR REPLACE VIEW test_view1 AS > SELECT status_to_flag(test_table.status) AS flag, > test_table.code_id > FROM test_table; > CREATE OR REPLACE VIEW test_view2 AS > SELECT * > FROM test_view1 > WHERE test_view1.flag = 1; I think the reason why the planner is afraid to flatten this is that the function is (by default) marked VOLATILE. Volatile functions in the select list are an optimization fence. That particular function looks like it should be IMMUTABLE instead, since it depends on no database state. If it does look at database state, you can probably use STABLE. http://www.postgresql.org/docs/9.0/static/xfunc-volatility.html regards, tom lane