Thread: 8.1 vs 8.2.1 view optimization
Firing up 8.2.1 I notice that sub-items in a view are optimized out if they aren't being selected. For example, "select item1, item2 from a_view" would take just as long as "select item1, item2, item3, item4 from a_view" This isn't usually a problem, but if item3 or item4 are significantly more complex (and slow) than item1 and item2 this is a big problem. In 8.1 and previous versions of postgresql this wouldn't happen. Is there some setting that can be set to re-enable this feature? Attached is a small sql script that shows the problem. When run on 8.2 or 8.2.1 it takes twice as long as when run on 8.1 because of this un-feature. This can be run on a completely fresh, blank database and it will create all of the languages, tables, etc that it needs. It will also clean up everything afterwards. The funciton "slow_function" is for illistration purposes only, and is made only to take time. Thanks in advance for any help, Nathan Bell IT Engineer Action Target, Inc. create trusted language 'plpgsql' handler plpgsql_call_handler lancompiler 'PL/pgSQL'; create table small ( only_item int4 ); create or replace function slow_function(int4, int4) returns int4 as $$ declare x int4; y int4; ret int4 := 2; begin for x in 1..$1 loop for y in 1..$2 loop ret := ret+(x/y); end loop; end loop; return ret; end; $$ language plpgsql; create or replace view small_v as select i.only_item as item1, slow_function(i.only_item,i.only_item) as item2 from small i ; insert into small values (1); insert into small values (3); insert into small values (10); insert into small values (25); insert into small values (100); insert into small values (250); insert into small values (1000); insert into small values (2500); select item1 from small_v; select item2 from small_v; drop view small_v; drop function slow_function(int4,int4); drop table small; drop language 'plpgsql';
Nathan Bell <nathanb@actarg.com> writes: > Firing up 8.2.1 I notice that sub-items in a view are optimized out if > they aren't being selected. You mean "not optimized out", I suppose. Declare your function as non-volatile if you want the optimizer to assume it's OK to discard. regards, tom lane
Yeah, I saw the "not optimized out" typo as soon as I hit send. What if the item that is taking a long time isn't a function, but rather a sub-select? Can I set the sub-select to stable, or perhaps set the entire view to non-volatile to achieve the same result? If not, can I set the sub-select to a different non-volatile view or do I need to create a non-volatile function that returns the result? Thanks for the help. Tom Lane wrote: >Nathan Bell <nathanb@actarg.com> writes: > > >>Firing up 8.2.1 I notice that sub-items in a view are optimized out if >>they aren't being selected. >> >> > >You mean "not optimized out", I suppose. Declare your function as >non-volatile if you want the optimizer to assume it's OK to discard. > > regards, tom lane > >
Nathan Bell <nathanb@actarg.com> writes: > What if the item that is taking a long time isn't a function, but rather > a sub-select? The point is that the view won't be flattened if there are nonvolatile functions in its SELECT list. regards, tom lane
I wrote: > The point is that the view won't be flattened if there are nonvolatile > functions in its SELECT list. Sheesh ... s/nonvolatile/volatile/ of course ... this thread seems afflicted with getting-it-backward disease :-( regards, tom lane
On Tue, Jan 16, 2007 at 02:55:08PM -0700, Nathan Bell wrote: > Yeah, I saw the "not optimized out" typo as soon as I hit send. > > What if the item that is taking a long time isn't a function, but rather > a sub-select? The planner should be able to see that the item is non-volatile itself. It only needs to be told for functions because it can't see into them. > Can I set the sub-select to stable, or perhaps set the entire view to > non-volatile to achieve the same result? No. > If not, can I set the sub-select to a different non-volatile view or do > I need to create a non-volatile function that returns the result? You could create a function that does the job, but that's generally not necessary. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.