where name like ? || '%'
But that still won't use an index [...]
Well, it depends.
From PostgreSQL 9.1 onwards, indexes (made by the trigram extension pg_trgm, an extension that comes with postgres) can be used in like searches. (careful, trigram indexes are large, and therefore not always appropriate).
A test with a 1-column table, filled with 80-characters 'random' strings; 1 million rows. There happened to be two rows that start with 'erix', so I'll use those as search target (i.e.: where txt like 'erix%').
-- index created like this:
create index azjunk6_trgm_re_idx
on azjunk6
using gin (txt gin_trgm_ops);
-- running a psql session, showing both
-- the regular output, and the EXPLAIN:
$ psql
Timing is on.
psql (9.2.4)
Type "help" for help.
testdb=# prepare ps(text) as
select txt from azjunk6 where txt like $1 || '%'; --> $1 is ? in
+DBI/DBD::Pg
PREPARE
Time: 13.785 ms
testdb=# execute ps('erix');
txt
----------------------------------------------------------------------
+------------
erixeweq jp ayoyu marqnrjr ughinmfwklvmzxtrfafy mvwf ond wrz lg yso
+tkgli ne n
erix onka fp ax erk ddcduajmv yrltcfjsfghlt pq cmdfximql jj ckyzhpwhc
+q zkakna
(2 rows)
Time: 54.482 ms
testdb=# explain analyze execute ps('erix');
QUERY PLAN
----------------------------------------------------------------------
+-------------------------------------------------------------
Bitmap Heap Scan on azjunk6 (cost=108.78..484.93 rows=100 width=81)
+(actual time=48.901..48.913 rows=2 loops=1)
Recheck Cond: (txt ~~ 'erix%'::text)
Rows Removed by Index Recheck: 16
-> Bitmap Index Scan on azjunk6_trgm_re_idx (cost=0.00..108.75 ro
+ws=100 width=0) (actual time=48.882..48.882 rows=18 loops=1)
Index Cond: (txt ~~ 'erix%'::text)
Total runtime: 48.957 ms
(6 rows)
Time: 49.714 ms -- look mum no hints
testdb=#
( And yes, it is faster: with the index removed, it takes 147.060 ms )
Even better: PostgreSQL 9.3 (in beta ATM) lets trgm index on regexen.