Thread: FW: view derived from view doesn't use indexes

<div class="WordSection1"><p class="MsoNormal">Using PG 9.0 and given the following definitions:<p
class="MsoNormal"> <pclass="MsoNormal">CREATE SEQUENCE test_code_id;<p class="MsoNormal"> <p class="MsoNormal">CREATE
TABLEtest_table<p class="MsoNormal">(<p class="MsoNormal" style="text-indent:36.0pt">status character(1) NOT NULL
DEFAULT'C'::bpchar,<p class="MsoNormal" style="text-indent:36.0pt">code_id integer NOT NULL DEFAULT
nextval('test_code_id')<pclass="MsoNormal">);<p class="MsoNormal"> <p class="MsoNormal">CREATE INDEX test1<p
class="MsoNormal"style="text-indent:36.0pt">ON test_table<p class="MsoNormal" style="text-indent:36.0pt">USING btree<p
class="MsoNormal"style="text-indent:36.0pt">(status, code_id);<p class="MsoNormal"> <p class="MsoNormal">CREATE OR
REPLACEFUNCTION status_to_flag(status character)<p class="MsoNormal" style="text-indent:36.0pt">RETURNS integer AS<p
class="MsoNormal"style="text-indent:36.0pt">$BODY$<p class="MsoNormal" style="text-indent:36.0pt">DECLARE flag
integer;<pclass="MsoNormal" style="text-indent:36.0pt">BEGIN<p class="MsoNormal"
style="margin-left:36.0pt;text-indent:36.0pt">CASEstatus<p class="MsoNormal"
style="margin-left:72.0pt;text-indent:36.0pt">WHEN'C' THEN<p class="MsoNormal"
style="margin-left:108.0pt;text-indent:36.0pt">flag= 1;<p class="MsoNormal" style="margin-left:108.0pt">WHEN 'U' THEN<p
class="MsoNormal"style="margin-left:108.0pt;text-indent:36.0pt">flag = 2;<p class="MsoNormal"
style="margin-left:108.0pt">WHEN'D' THEN<p class="MsoNormal" style="margin-left:108.0pt;text-indent:36.0pt">flag = 3;<p
class="MsoNormal"style="margin-left:108.0pt">ELSE<p class="MsoNormal"
style="margin-left:108.0pt;text-indent:36.0pt">CASE<pclass="MsoNormal"
style="margin-left:144.0pt;text-indent:36.0pt">WHENstatus SIMILAR TO '[0-9]+' THEN<p class="MsoNormal"
style="margin-left:180.0pt;text-indent:36.0pt">flag= CAST(status as integer);<p class="MsoNormal"
style="margin-left:180.0pt">ELSE<pclass="MsoNormal" style="margin-left:180.0pt;text-indent:36.0pt">flag = 0;<p
class="MsoNormal"style="margin-left:108.0pt;text-indent:36.0pt">END CASE;<p class="MsoNormal"
style="margin-left:36.0pt;text-indent:36.0pt">ENDCASE;<p class="MsoNormal"
style="margin-left:36.0pt;text-indent:36.0pt"> <pclass="MsoNormal" style="margin-left:36.0pt;text-indent:36.0pt">RETURN
flag;<pclass="MsoNormal" style="text-indent:36.0pt">END; <p class="MsoNormal" style="text-indent:36.0pt">$BODY$<p
class="MsoNormal">LANGUAGEplpgsql<p class="MsoNormal"> <p class="MsoNormal">CREATE OR REPLACE VIEW test_view1 AS <p
class="MsoNormal"style="text-indent:36.0pt">SELECT status_to_flag(test_table.status) AS flag,<p class="MsoNormal"
style="text-indent:36.0pt">test_table.code_id<pclass="MsoNormal" style="text-indent:36.0pt">FROM test_table;<p
class="MsoNormal"> <pclass="MsoNormal">CREATE OR REPLACE VIEW test_view2 AS <p class="MsoNormal"
style="text-indent:36.0pt">SELECT*<p class="MsoNormal" style="text-indent:36.0pt">FROM test_view1<p class="MsoNormal"
style="text-indent:36.0pt">WHEREtest_view1.flag = 1;<p class="MsoNormal"> <p class="MsoNormal">CREATE OR REPLACE VIEW
test_view3AS <p class="MsoNormal" style="text-indent:36.0pt">SELECT status_to_flag(test_table.status) AS flag,<p
class="MsoNormal"style="text-indent:36.0pt">test_table.code_id<p class="MsoNormal" style="text-indent:36.0pt">FROM
test_table<pclass="MsoNormal" style="text-indent:36.0pt">WHERE test_table.flag = 1;<p class="MsoNormal"
style="text-indent:36.0pt"> <pclass="MsoNormal">--To populate the table, copy / paste the following lines multiple
times:<pclass="MsoNormal" style="margin-left:36.0pt">insert into test_table (status) VALUES ('C');<p class="MsoNormal"
style="margin-left:36.0pt">insertinto test_table (status) VALUES ('D');<p class="MsoNormal"
style="margin-left:36.0pt">insertinto test_table (status) VALUES ('U');<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal">--Asequential scan is performed when a query is run against test_view2 such as:<p
class="MsoNormal">               SELECT * from test_view2<p class="MsoNormal">                WHERE code_id >
10000<pclass="MsoNormal">                AND code_id < 10010;<p class="MsoNormal"> <p class="MsoNormal">--Whereas,
ifyou run the same query against test_view3 the index is used:<p class="MsoNormal">                SELECT * from
test_view3<pclass="MsoNormal">                WHERE code_id > 10000<p class="MsoNormal">                AND code_id
<10010;<p class="MsoNormal"> <p class="MsoNormal">As our tables / views are obviously a lot more complicated that
theexamples above this causes up problems as we have to create all views to point directly to the table rather than
derivingthem from other views.<p class="MsoNormal"> <p class="MsoNormal">Any ideas?<p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"><span style="font-size:10.0pt">Regards,</span><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";color:#365F91"> </span><p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><spanstyle="font-size:10.0pt;font-family:"Times New
Roman","serif";color:black">RussellKeane</span></b><span style="font-size:9.0pt;color:#E36C0A"> </span><p
class="MsoNormal"> </div><br/><hr /><font color="Black" face="Arial" size="2">Registered name: In Practice Systems
Ltd.<br/> Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ<br /> Registered Number:
1788577<br/> Registered in England<br /> Visit our Internet Web site at www.inps.co.uk<br /> The information in this
internetemail is confidential and is intended solely for the addressee. Access, copying or re-use of information in it
byanyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily
representthose of INPS or any of its affiliates. If you are not the intended recipient please contact
is.helpdesk@inps.co.uk<br/><br /></font> 

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

Hi Tom,

Thanks for that, that was exactly the issue.
It absolutely is IMMUTABLE and changing it from VOLATILE has fixed the issue.

Much appreciated,

Russell,



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 26 July 2012 16:52
To: Russell Keane
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] FW: view derived from view doesn't use indexes

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.
Volatilefunctions in the select list are an optimization fence.  That particular function looks like it should be
IMMUTABLEinstead, 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