| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Listmail <lists(at)peufeu(dot)com> |
| Cc: | "John Cole" <john(dot)cole(at)uai(dot)com>, "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Query in function not using index... |
| Date: | 2007-04-27 22:56:42 |
| Message-ID: | 6716.1177714602@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Listmail <lists(at)peufeu(dot)com> 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Josh Berkus | 2007-04-28 00:46:17 | Re: Feature Request --- was: PostgreSQL Performance Tuning |
| Previous Message | Arturo Munive | 2007-04-27 20:36:54 | Indice en Date |