From: Markus Bertheau <mbertheau.pg@googlemail.com>
To: Pavel Rotek <pavel.rotek@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: response time when querying via JDBC and via psql differs
Date: Mon, 25 Feb 2008 17:10:32 +0600
Message-ID: <684362e10802250310m48c45cb3lad3e70312ef4b3b6@mail.gmail.com> (raw)
In-Reply-To: <3556c08c0802250206w1668aaday51dc9be9fddd7b70@mail.gmail.com>
References: <3556c08c0802250206w1668aaday51dc9be9fddd7b70@mail.gmail.com>
2008/2/25, Pavel Rotek <pavel.rotek@gmail.com>:
> I have created functional index table(lower(href) varchar_pattern_ops)
> because of lower case "like" searching. When i ask the database directly
> from psql, it returns result in 0,5 ms, but when i put the same command via
> jdbc driver, it returns in 10 000 ms. Where can be the problem?? Any problem
> with PostgreSQL tuning??
Most likely the problem is that the JDBC driver uses prepared statements, in
which the query is planned withouth the concrete argument value. For like only
patterns that don't start with % or _ can use the index. Without the argument
value PostgreSQL can't tell whether that is the case, so it takes the safe
route and chooses a sequential scan.
to solve this particular problem, you have to convince jdbc to not use a
prepared statement for this particular query.
Markus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mbertheau.pg@googlemail.com, pavel.rotek@gmail.com
Subject: Re: response time when querying via JDBC and via psql differs
In-Reply-To: <684362e10802250310m48c45cb3lad3e70312ef4b3b6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox