From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: index scan with functional indexes |
Date: | 2004-01-27 18:09:13 |
Message-ID: | 1075226952.1610.296.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tried, all the suggestions
--dc--
davec=# explain analyze select * from url where fn_strrev(url) like
fn_strrev('%beta12.html');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on url (cost=100000000.00..100013533.04 rows=503 width=454)
(actual time=1416.448..3817.221 rows=12 loops=1)
Filter: (fn_strrev((url)::text) ~~ 'lmth.21ateb%'::text)
Total runtime: 3817.315 ms
(3 rows)
davec=# explain analyze select * from url where fn_strrev(url) like
'lmth.21ateb%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on url (cost=100000000.00..100013533.04 rows=503 width=454)
(actual time=1412.181..3843.998 rows=12 loops=1)
Filter: (fn_strrev((url)::text) ~~ 'lmth.21ateb%'::text)
Total runtime: 3844.106 ms
(3 rows)
davec=# explain analyze select * from url where fn_strrev(url) like
'%lmth.21ateb';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on url (cost=100000000.00..100013533.04 rows=503 width=454)
(actual time=3853.501..3853.501 rows=0 loops=1)
Filter: (fn_strrev((url)::text) ~~ '%lmth.21ateb'::text)
Total runtime: 3853.583 ms
(3 rows)
On Tue, 2004-01-27 at 13:02, Tom Lane wrote:
> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> > davec=# explain analyze select * from url where fn_strrev(url) like
> > '%beta12.html';
>
> Don't you need the % at the right end to have an indexable plan?
> I suspect that both of your tries so far are actually semantically
> wrong, and that what you intend is
>
> select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
>
> regards, tom lane
>
--
Dave Cramer
519 939 0336
ICQ # 1467551
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-01-27 18:13:45 | Re: Function call |
Previous Message | Tom Lane | 2004-01-27 18:06:11 | Re: Another optimizer question |