From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: expression index not used within function |
Date: | 2013-11-14 00:36:29 |
Message-ID: | 29517.1384389389@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David Johnston <polobo(at)yahoo(dot)com> writes:
> LPlateAndy wrote
>> When i call SELECT * FROM _function(IN TEXT) it is very slow on my WHERE
>> clause using LIKE($1||'%') but (run in error), very fast when LIKE('some
>> text'||'%')
> The index cannot be used for LIKE ($1 || '%') because there is no way the
> planner can guarantee the value of $1 isn't something like "%mid" which
> would resolve to "LIKE (%mid%)" which is a mid-string search which the index
> will not help with.
> If you place a constant at the front of the like pattern it can use the
> index to get into the region with the matching prefix.
Also, if you use 9.2 or later, the planner should be able to get the
desired result by re-planning the statement each time (so that it can
treat the current value of $1 as a constant). If this is 9.2+, and
that doesn't seem to be happening, it would be worth presenting a
complete example so that we can diagnose why not.
(Pre-9.2, the traditional advice for forcing a custom plan each time
is to use EXECUTE. That's not the optimal way anymore, though.)
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2013-11-14 00:39:41 | Re: what checksum algo? |
Previous Message | Jeff Janes | 2013-11-14 00:26:08 | Re: freeze cannot be finished |