Postgres Pro
Downloads
Home   >   mailing lists

Thread: Where col like 'abc%' with PreparedStatement

Where col like 'abc%' with PreparedStatement

From
Andy Fan
Date:
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? 

Thanks

Re: Where col like 'abc%' with PreparedStatement

From
Tom Lane
Date:
Andy Fan <zhihui.fan1213@gmail.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

Re: Where col like 'abc%' with PreparedStatement

From
Andy Fan
Date:


On Mon, Mar 22, 2021 at 11:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andy Fan <zhihui.fan1213@gmail.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