Re: Irrelevant columns cause massive performance change

On Fri, Mar 16, 2018 at 1:50 PM, Andres Freund <andres(at)anarazel(dot)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?
>

Yes, perfect. That fixed the problem.

Thanks,
Craig

>
> Greetings,
>
> Andres Freund
>

--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------