Re: Where col like 'abc%' with PreparedStatement

On Mon, Mar 22, 2021 at 11:56 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> > The real SQL I want to execute is SELECT * FROM t WHERE col like 'abc%';
> > This query
> > probably can go with IndexScan since it is prefixed with 'abc'. However
> I
> > am confused when I want
> > to use PreparedStatement.
>
> > For example:
> > List<Map<String, Object>> imap =
> jdbcTemplate.queryForList("select *
> > from tm where a like ?",
> > new Object[] {"a" + "%"}
> > );
>
> > The parse tree is sent to postgreSQL is "a like ?", server knows nothing
> > about if it is prefixed with
> > something or not, so probably Index Scan can't be selected. So what the
> > user can use the prepared
> > Statement and use the IndexScan for the above case?
>
> The plancache's "custom plan vs generic plan" mechanism is meant to
> deal with cases like that. It doesn't always get it right, but if
> you consistently use actual values like "abc%" then it will notice
> that inserting the parameter value produces a significantly better
> plan, and do things that way instead of making a generic plan that
> doesn't depend on the parameter value.
>
> regards, tom lane
>

Thank you Tom! Actually I have set plan_cache_mode to force_generic_plan
and I do want 1). Use a generic plan to save the planning cost. 2). still
use the
IndexScan. 3). we CAN assume the user ALWAYS input "abc%"-like value (not
%abc%') for my case. I am searching for a method to tell this fact to the
server. Is it something we can do?

--
Best Regards
Andy Fan (https://www.aliyun.com/)