From: Tom Lane <tgl@sss.pgh.pa.us>
To: Russell Keane <Russell.Keane@inps.co.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: FW: view derived from view doesn't use indexes
Date: Thu, 26 Jul 2012 11:51:40 -0400
Message-ID: <22988.1343317900@sss.pgh.pa.us> (raw)
In-Reply-To: <8D0E5D045E36124A8F1DDDB463D548557CEF4D2D44@mxsvr1.is.inps.co.uk>
References: <8D0E5D045E36124A8F1DDDB463D548557CEF4D2D44@mxsvr1.is.inps.co.uk>
Russell Keane <Russell.Keane@inps.co.uk> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Russell.Keane@inps.co.uk
Subject: Re: FW: view derived from view doesn't use indexes
In-Reply-To: <22988.1343317900@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox