Date: Fri, 16 Mar 2018 13:50:13 -0700
From: Andres Freund <andres@anarazel.de>
To: Craig James <cjames@emolecules.com>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
Subject: Re: Irrelevant columns cause massive performance change
Message-ID: <20180316205013.sl7vrlr44ms3ptkg@alap3.anarazel.de>
References: 
 <CAFwQ8rdgsRC+zJrcmqpRx8GeU0h_OSnhsuW8GQgLze9RzCUmYQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFwQ8rdgsRC+zJrcmqpRx8GeU0h_OSnhsuW8GQgLze9RzCUmYQ@mail.gmail.com>
Precedence: bulk

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