FW: view derived from view doesn't use indexes - Mailing list pgsql-sql

<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>