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

Using PG 9.0 and given the following definitions:

CREATE SEQUENCE test_code_id;

CREATE TABLE test_table
(
status character(1) NOT NULL DEFAULT 'C'::bpchar,
code_id integer NOT NULL DEFAULT nextval('test_code_id')
);

CREATE INDEX test1
ON test_table
USING btree
(status, code_id);

CREATE OR REPLACE FUNCTION status_to_flag(status character)
RETURNS integer AS
$BODY$
DECLARE flag integer;
BEGIN
CASE status
WHEN 'C' THEN
flag = 1;
WHEN 'U' THEN
flag = 2;
WHEN 'D' THEN
flag = 3;
ELSE
CASE
WHEN status SIMILAR TO '[0-9]+' THEN
flag = CAST(status as integer);
ELSE
flag = 0;
END CASE;
END CASE;

RETURN flag;
END;
$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;

CREATE OR REPLACE VIEW test_view3 AS
SELECT status_to_flag(test_table.status) AS flag,
test_table.code_id
FROM test_table
WHERE test_table.flag = 1;

--To populate the table, copy / paste the following lines multiple times:
insert into test_table (status) VALUES ('C');
insert into test_table (status) VALUES ('D');
insert into test_table (status) VALUES ('U');

--A sequential scan is performed when a query is run against test_view2 such as:
SELECT * from test_view2
WHERE code_id > 10000
AND code_id < 10010;

--Whereas, if you run the same query against test_view3 the index is used:
SELECT * from test_view3
WHERE code_id > 10000
AND code_id < 10010;

As our tables / views are obviously a lot more complicated that the examples above this causes up problems as we have to create all views to point directly to the table rather than deriving them from other views.

Any ideas?

Regards,

Russell Keane

________________________________
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is not authorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of INPS or any of its affiliates. If you are not the intended recipient please contact is(dot)helpdesk(at)inps(dot)co(dot)uk

Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 26 July 2012 16:52
To: Russell Keane
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] FW: view derived from view doesn't use indexes

Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)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