| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Jie Liang <jliang(at)ipinc(dot)com> |
| Cc: | edipoelder(at)ig(dot)com(dot)br, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: using for rec inside a function: behavior very slow |
| Date: | 2001-04-03 01:16:56 |
| Message-ID: | 24884.986260616@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin pgsql-sql |
Jie Liang <jliang(at)ipinc(dot)com> writes:
> v_url:= $1||''%'';
> for rec in select id,url from urlinfo where url like v_url order by
> url loop
[ is slow ]
LIKE index optimization doesn't happen if the LIKE pattern is a variable
when the plan is created.
In 7.1 you can work around this problem by using plpgsql's FOR ... EXECUTE
notation, but I don't think there's any good answer in 7.0.
for rec in execute ''select id,url from urlinfo where url like ''||quote_literal(v_url)||'' order by url'' loop
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jean-Roch SCHMITT | 2001-04-03 05:46:04 | base/1 instead of base/template1 initdb result |
| Previous Message | Jie Liang | 2001-04-03 00:04:13 | select statement inside a function: behavior bad |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2001-04-03 01:19:07 | Re: very very slow ..... |
| Previous Message | Jie Liang | 2001-04-03 00:04:13 | select statement inside a function: behavior bad |