Tom,
We were looking at the explain results and noticed that it was converting
the 'wynn%' into fname >= 'wynn' and fname < 'wyno'
So we modified our function to mimic this transformation, where we have
two variables that contain the 'wynn' and 'wyno':
Fname >= @var1 and fname < @var2
This seems to pick up the index ok, but is this a valid and reliable
solution? Or is there another/better way to do this?
Thanks for the explanation, it really helps.
John
-----Original Message-----
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, April 27, 2007 5:57 PM
To: Listmail
Cc: John Cole; 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Query in function not using index...
Listmail <[EMAIL PROTECTED]> writes:
>> Any idea why using a variable v_streetName instead of a string 'wynn%'
>> behaves differently?
> Yeah.
> 'wynn%' doesn't start with % so LIKE uses index.
> But Postgres doesn't know that you know that the contents of this
> variable never starts with '%'...
Even more to the point: the transformation of LIKE 'pattern' into an
index range is a plan-time optimization, so if the planner hasn't got a
constant pattern to work with, it'll never happen. (Yeah, this should
be improved someday.)
If you need to depend on LIKE indexing with non-constant patterns,
you'll have to use EXECUTE to force the query to be replanned each time.
regards, tom lane
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007
5:26 PM
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007
5:26 PM
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email in error please notify the sender. This message
contains confidential information and is intended only for the individual
named. If you are not the named addressee you should not disseminate,
distribute or copy this e-mail.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
