Re: Where col like 'abc%' with PreparedStatement
От | Tom Lane |
---|---|
Тема | Re: Where col like 'abc%' with PreparedStatement |
Дата | |
Msg-id | 574558.1616428566@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Where col like 'abc%' with PreparedStatement (Andy Fan <zhihui.fan1213@gmail.com>) |
Ответы |
Re: Where col like 'abc%' with PreparedStatement
|
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: