Re: Irrelevant columns cause massive performance change

On Fri, Mar 16, 2018 at 1:50 PM, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-03-16 13:37:05 -0700, Craig James wrote:
> The timing of the second query is excellent, and is what I expected. I
> don't understand why including a function-defined column in the view would
> have such a dramatic effect on the planner's ability to choose the sdf_pkey
> index for the join.

> create or replace function gunzip(bytea) returns text as
> $gunzip$
>   use IO::Uncompress::Gunzip qw(gunzip $GunzipError);
>   my $compressed = decode_bytea($_[0]);
>   my $uncompressed;
>   if (!gunzip(\$compressed, \$uncompressed)) {
>     return $GunzipError;
>   }
>   return $uncompressed;
> $gunzip$
> language plperlu;

I suspect at least part of the problem here is that the function is
declared volatile (the default). That means it can have arbitrary
sideeffects, which in turn means there's several places in the planner
that forgo optimizations if volatile functions are involved.  If you
declare the function as immutable, does the problem persist?

Greetings,

Andres Freund