Re: index scan with functional indexes

same answer

davec=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)

davec=# explain analyze select * from url where fn_strrev(url) like
'%beta12.html';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on url (cost=100000000.00..100013533.04 rows=503 width=454)
(actual time=3851.636..3851.636 rows=0 loops=1)
Filter: (fn_strrev((url)::text) ~~ '%beta12.html'::text)
Total runtime: 3851.712 ms
(3 rows)

On Tue, 2004-01-27 at 12:33, Tom Lane wrote:
> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> > create index r_url_idx on url( fn_strrev(url));
>
> > explain select * from url where url like fn_strrev('%beta12.html');
> > QUERY PLAN
> > ---------------------------------------------------------
> > Seq Scan on url (cost=0.00..13281.70 rows=1 width=454)
> > Filter: ((url)::text ~~ 'lmth.21ateb%'::text)
>
> > Is it possible to get the planner to use an index scan ?
>
> Sure, but not that way. Try "fn_strrev(url) like something".
> You have to compare the indexed value to something...
>
> regards, tom lane
>
--
Dave Cramer
519 939 0336
ICQ # 1467551