Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 75EB99FA376 for ; Sun, 5 Nov 2006 21:34:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 80916-02 for ; Sun, 5 Nov 2006 21:33:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 58AC39F9EEC for ; Sun, 5 Nov 2006 21:33:57 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x37so2543524nfc for ; Sun, 05 Nov 2006 17:33:56 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:references; b=RHsd4YJhbLeAoJaog0SOV5X5Z1zKya2O8dJk3YBZ4BKrXLYziXEBmf/02xCAinWYJFvp5JaPSGTtKtCGk4hFvZNaB4TrsGDZHlWaRL/Gj9MrshFcBcOTK6gGLXOxkkokueY8c8Hj79sd4R3NvDLZSLxDN/uQO6HCxreVLcwd+NQ= Received: by 10.49.29.3 with SMTP id g3mr5199922nfj.1162776835649; Sun, 05 Nov 2006 17:33:55 -0800 (PST) Received: by 10.49.78.1 with HTTP; Sun, 5 Nov 2006 17:33:55 -0800 (PST) Message-ID: <430d92a20611051733y2af2e110ie605abeacbecc939@mail.gmail.com> Date: Sun, 5 Nov 2006 20:33:55 -0500 From: Gene Reply-To: gene@sotech.us To: pgsql-performance@postgresql.org Subject: Re: Slow functional indexes? In-Reply-To: <26921.1162776224@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_63747_22024965.1162776835354" References: <45387666.204@canonical.com> <454E4AC7.9020201@stuartbishop.net> <26921.1162776224@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/56 X-Sequence-Number: 21826 ------=_Part_63747_22024965.1162776835354 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a varchar field which is most commonly queried like "someField like '%abcd'". Realizing that it wouldn't be able to use an index for this type of query I created a reverse() function and an index using the function reverse(someField) so that the query would be performed as "reverse(someField) like reverse('%abcd')". When I looked at the query plan it seemed like it was using the new reverse index properly but also seemed to run slower. Would this explain these bazaar results? I have since gone back to the method without using the reverse function. Thanks On 11/5/06, Tom Lane wrote: > > Stuart Bishop 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Gene Hart cell: 443-604-2679 ------=_Part_63747_22024965.1162776835354 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a varchar field which is most commonly queried like "someField like '%abcd'". Realizing that it wouldn't be able to use an index for this type of query I created a reverse() function and an index using the function reverse(someField) so that the query would be performed as "reverse(someField) like reverse('%abcd')". When I looked at the query plan it seemed like it was using the new reverse index properly but also seemed to run slower. Would this explain these bazaar results? I have since gone back to the method without using the reverse function. Thanks

On 11/5/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly



--
Gene Hart
cell: 443-604-2679 ------=_Part_63747_22024965.1162776835354--