index scan with functional indexes

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: index scan with functional indexes
Date: 2004-01-27 14:26:50
Message-ID: 1075213610.1611.287.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm curious what the result of a reverse index does on a table with url
like data, so I did the following

create function fn_strrev(text) returns text as 'return reverse($_[0])'
language 'plperl' with (iscachable);

create index r_url_idx on url( fn_strrev(url));

vacuum analyze;

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 ?

How?

the db is using locale 'C'

--
Dave Cramer
519 939 0336
ICQ # 1467551

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dennis Haney 2004-01-27 14:32:54 Re: Recursive optimization of IN subqueries
Previous Message Dave Cramer 2004-01-27 14:21:55 building plperl on 7.4.1