Re: Speeding up LIKE with placeholders?

If I understand correctly your problem is that the plan for your prepared
query is bad because the LIKE parameter is not known at prepare time...

Immediate solutions :

Is it possible to use EXECUTE in your SQL to sidestep this and replan the
query with the right values ?
Can you put your query in a function so that it's planned on first
execution with real parameter values ?

There is still the old :
SELECT ... WHERE field BETWEEN 'prefix' AND 'prefiy'; but it kinda sucks
;)

---------------------------
Maybe this just indicates a lack of user-friendliness in the API ?
Suggestions for improvement (in random order):
Don't flame me for suggesting impossible things... I'm just wishing.

* Maybe a new operator like <column STARTSWITH 'prefix'> could replace
<column LIKE 'prefix%'> and tell the planner with absolute certainty that,
whatever the value is, use of an index is possible ? It's possible to
create user defined operators in Postgres... so...

* In a future version, when planning prepared queries, maybe the planner
could flag a query as "replan according to real parameter values every
time it's executed" when it detects a hard to plan condition like "LIKE
$1" or other hard to predict cases ? In this case the cost of planning
would occur at each execution, but the query parsing time would still be
saved. Or maybe the planner could write itself a note saying : "I planned
the query for the generic LIKE, but if $1 does not start with a %, I
should replan it when it's executed"

* Maybe there should be a keyword like "REPLAN" to allow the user to
specify that the query should be replanned every time ?

* Maybe there should be a keyword like "DEFER PLAN" to allow the user to
specify that the query should be not be planned when PREPARE is called,
but rather on its first execution, with real parameters, and the plan then
stored and used for subsequent queries ?
This has the disadvantage of depending on the next query to specify
adequate parameters.

Maybe we could tell the planner which parameters to use for preparing the
plan :
PREPARE myquery( text, integer )
WITH PARAMETERS ('aa%', 5)
AS SELECT * FROM mytable WHERE ...

so the plan stored for this prepared query would be generated using the
specified parameter values instead of just placeholders (so the planner
may generate a better plan, in this case it would notice the prefix nature
of the LIKE 'aa%').

* Maybe we could give the planner hints about the placeholders in a
prepared query. Something like

PREPARE myquery( text, integer )
AS SELECT * FROM mytable WHERE ...

would become :

PREPARE myquery( text, integer )
WHERE expression
AS SELECT * FROM mytable WHERE ...

where expression would be any boolean expression giving hints to the
planner, like :
$2 NOT NULL
in this case the planner would know that it should take into account only
the stats for $2 not null,10 ; and ignore the non-indexed NULLs for
example. However this is redundant as the "$2 NOT NULL" can as well be put
in the WHERE part of the query.

This could be used in the 'LIKE $1' case by specifying that $1 cannot
start with a '%'. The planner would have to be pretty smart to take this
into account...
When the expression is false, the query would be re-planned instead of
using the stored plan.

* Maybe we could add a clause to this poster's SELECT :

PREPARE myquery AS SELECT * FROM mytable
WHERE myfield LIKE $1 AND NOT ($1 LIKE '%%%');

In this case the planner would have to recognize the "NOT ($1 LIKE
'%%%')" part and take appropriate measures... When executing the query
this would not add any cost as it is a constant. However if the user
messes up and sends $1='%something', he'll get no results.

Anyway,
have a nice day !