From: | Craig James <cjames(at)emolecules(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Irrelevant columns cause massive performance change |
Date: | 2018-03-16 21:06:04 |
Message-ID: | CAFwQ8rfKY1pAR63u9XLJA9eF0mnT=LMWzUX_M7+O4qNoZ7MAGQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
---------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | David Osborne | 2018-03-19 15:13:37 | Slow performance after restoring a dump |
Previous Message | Andres Freund | 2018-03-16 20:50:13 | Re: Irrelevant columns cause massive performance change |