Re: Irrelevant columns cause massive performance change - Mailing list pgsql-performance

From Andres Freund
Subject Re: Irrelevant columns cause massive performance change
Date
Msg-id 20180316205013.sl7vrlr44ms3ptkg@alap3.anarazel.de
Whole thread Raw
In response to Irrelevant columns cause massive performance change  (Craig James <cjames@emolecules.com>)
Responses Re: Irrelevant columns cause massive performance change  (Craig James <cjames@emolecules.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Craig James
Date:
Subject: Irrelevant columns cause massive performance change
Next
From: Craig James
Date:
Subject: Re: Irrelevant columns cause massive performance change