Re: performance issue

[Kris Jurka <books(at)ejurka(dot)com>]

Steven varga wrote:
> having about 10^6 records in a table indexed on names and doing a
> query from psql I get response time in millisec order on the other
> hand when executing the same query through JDBC it hangs about 80 
> seconds.
>
>  PreparedStatement count =
>         connection.prepareStatement("SELECT count(*) FROM
>     upc WHERE name like upper(?)||'%' ");

When using a PreparedStatement the server must come up with a plan that 
works for all parameter values.  Since the parameter is unknown, the 
generated plan doesn't use an index.  Your options are to interpolate 
the parameter yourself or connect using the protocolVersion=2 URL option 
which will make the driver do the interpolation prior to passing the 
query on to the server.

Kris Jurka