Skip site navigation (1) Skip section navigation (2)

Re: query plans and immutable functions

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Reece Hart <reece(at)harts(dot)net>
Cc: SF PostgreSQL <sfpug(at)postgresql(dot)org>
Subject: Re: query plans and immutable functions
Date: 2006-05-10 00:16:07
Message-ID: 446130C7.9090603@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
Reece,

> When md5() is explicitly cast to bpchar, the index is used.  I ran
> across this issue long ago (in 7.x), but I thought that casting was now
> part of query optimization and that explicit casting was no longer
> required.  I was wrong.

Well, the planner tries to figure out the casting.  Sometimes it fails. 
     CHAR is particularly problematic in this regard; it's not exactly 
equivalent to TEXT so the plannner can fail to match it up.

Your solution of course, is to create a shell function for md5 which 
converts it to CHAR.  Or just cast.

--Josh Berkus

In response to

sfpug by date

Next:From: David FetterDate: 2006-05-10 20:53:04
Subject: [JOB] Linux Engineer, Safeway
Previous:From: eleinDate: 2006-05-09 18:49:46
Subject: Re: query plans and immutable functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group