From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stuart Bishop <stuart@stuartbishop.net>
Cc: Stuart Bishop <stuart.bishop@canonical.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow functional indexes?
Date: Sun, 05 Nov 2006 20:23:44 -0500
Message-ID: <26921.1162776224@sss.pgh.pa.us> (raw)
In-Reply-To: <454E4AC7.9020201@stuartbishop.net>
References: <45387666.204@canonical.com>
<454E4AC7.9020201@stuartbishop.net>
Stuart Bishop <stuart@stuartbishop.net> writes:
> Here is a minimal test case that demonstrates the issue. Can anyone else
> reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at
> the end, the one that orders by a user created IMMUTABLE stored procedure is
> consistently slower than the other three variants.
Wow, interesting. I'm surprised we never realized this before, but
here's the deal: the generated plan computes the ORDER BY expressions
even if we end up not needing them because the ordering is created by
an indexscan rather than an explicit sort step. (Such a sort step would
of course need the values as input.) So the differential you're seeing
represents the time for all those useless evaluations of the function.
The difference in the estimated cost comes from that too --- the code
doing the estimation can see perfectly well that there's an extra
function call in the plan ...
Not sure whether there's a simple way to fix this; it might take some
nontrivial rejiggering in the planner. Or maybe not, but I don't have
any cute ideas about it at the moment.
I wonder whether there are any other cases where we are doing useless
computations of resjunk columns?
regards, tom lane
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: tgl@sss.pgh.pa.us, stuart@stuartbishop.net, stuart.bishop@canonical.com
Subject: Re: Slow functional indexes?
In-Reply-To: <26921.1162776224@sss.pgh.pa.us>
* 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