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
Date: Fri, 16 Mar 2018 13:50:13 -0700
Message-ID: <20180316205013.sl7vrlr44ms3ptkg@alap3.anarazel.de> (raw)
In-Reply-To: <CAFwQ8rdgsRC+zJrcmqpRx8GeU0h_OSnhsuW8GQgLze9RzCUmYQ@mail.gmail.com>
References: <CAFwQ8rdgsRC+zJrcmqpRx8GeU0h_OSnhsuW8GQgLze9RzCUmYQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@anarazel.de, cjames@emolecules.com
Subject: Re: Irrelevant columns cause massive performance change
In-Reply-To: <20180316205013.sl7vrlr44ms3ptkg@alap3.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox