8.1 vs 8.2.1 view optimization

Поиск
Список
Период
Сортировка
От Nathan Bell
Тема 8.1 vs 8.2.1 view optimization
Дата
Msg-id 45AD42A5.8000705@actarg.com
обсуждение исходный текст
Ответы Re: 8.1 vs 8.2.1 view optimization  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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';

В списке pgsql-general по дате отправления:

Предыдущее
От: Stephan Szabo
Дата:
Сообщение: Re: Multi-column constraint behaviour
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Dynamic loading of Perl Code in Postgres functions