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?
