Re: index scan with functional indexes

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