Skip site navigation (1) Skip section navigation (2)

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

From: Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: FW: view derived from view doesn't use indexes
Date: 2012-07-27 10:33:07
Message-ID: 8D0E5D045E36124A8F1DDDB463D548557CEF4D2D95@mxsvr1.is.inps.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: AndreasDate: 2012-07-28 01:57:00
Subject: join against a function-result fails
Previous:From: Tom LaneDate: 2012-07-26 15:51:40
Subject: Re: FW: view derived from view doesn't use indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group