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@fastcrypt.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