Skip site navigation (1) Skip section navigation (2)

Re: index scan with functional indexes

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 (view raw or flat)
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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-01-27 18:13:45
Subject: Re: Function call
Previous:From: Tom LaneDate: 2004-01-27 18:06:11
Subject: Re: Another optimizer question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group