pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedresponse time when querying via JDBC and via psql differs
4+ messages / 4 participants
[nested] [flat]
* response time when querying via JDBC and via psql differs
@ 2008-02-25 10:06 Pavel Rotek <pavel.rotek@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Pavel Rotek @ 2008-02-25 10:06 UTC (permalink / raw)
To: pgsql-performance
Hi all,
i have strange problem with performance in PostgreSQL (8.1.9). My problem
shortly:
I'm using postgreSQL via JDBC driver (postgresql-8.1-404.jdbc3.jar) and
asking the database for search on table with approximately 3 000 000
records.
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??
The command is
select df.id as id, df.c as c, df.href as href, df.existing as existing,
df.filesize as filesize from documentfile df where (lower(href) like
'aba001!_2235800001.djvu' escape '!' ) order by id limit 1 Thank you very
much for any help,
Kind regards,
Pavel Rotek
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: response time when querying via JDBC and via psql differs
@ 2008-02-25 11:10 Markus Bertheau <mbertheau.pg@googlemail.com>
parent: Pavel Rotek <pavel.rotek@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Markus Bertheau @ 2008-02-25 11:10 UTC (permalink / raw)
To: Pavel Rotek <pavel.rotek@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: response time when querying via JDBC and via psql differs
@ 2008-02-25 14:11 Nikolas Everett <nik9000@gmail.com>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Nikolas Everett @ 2008-02-25 14:11 UTC (permalink / raw)
To: Markus Bertheau <mbertheau.pg@googlemail.com>; +Cc: Pavel Rotek <pavel.rotek@gmail.com>; pgsql-performance
The thing to remember here is that prepared statements are only planned once
and strait queries are planned for each query.
When you give the query planner some concrete input like in your example
then it will happily use the index because it can check if the input starts
with % or _. If you use JDBC to set up a prepared statement like:
> select df.id as id, df.c as c, df.href as href, df.existing as existing,
> df.filesize as filesize from documentfile df where (lower(href) like ?
> escape '!' ) order by id limit 1
then the query planner takes the safe route like Markus said and doesn't use
the index.
I think your best bet is to use connection.createStatement instead of
connection.prepareStatement. The gain in query performance will offset the
loss in planning overhead. I'm reasonably sure the plans are cached anyway.
--Nik
On Mon, Feb 25, 2008 at 6:10 AM, Markus Bertheau <
mbertheau.pg@googlemail.com> wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: response time when querying via JDBC and via psql differs
@ 2008-02-26 13:26 valgog <valgog@gmail.com>
parent: Pavel Rotek <pavel.rotek@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: valgog @ 2008-02-26 13:26 UTC (permalink / raw)
To: pgsql-performance
Do not use setString() method to pass the parameter to the
PreparedStatement in JDBC. Construct an SQL query string as you write
it here and query the database with this new SQL string. This will
make the planner to recreate a plan every time for every new SQL
string per session (that is not usually good) but it will make the
planner to choose a correct plan.
-- Valentine Gogichashvili
On Feb 25, 11:06 am, pavel.ro...@gmail.com ("Pavel Rotek") wrote:
> Hi all,
>
> i have strange problem with performance in PostgreSQL (8.1.9). My problem
> shortly:
>
> I'm using postgreSQL via JDBC driver (postgresql-8.1-404.jdbc3.jar) and
> asking the database for search on table with approximately 3 000 000
> records.
> 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??
>
> The command is
> select df.id as id, df.c as c, df.href as href, df.existing as existing,
> df.filesize as filesize from documentfile df where (lower(href) like
> 'aba001!_2235800001.djvu' escape '!' ) order by id limit 1 Thank you very
> much for any help,
>
> Kind regards,
>
> Pavel Rotek
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-02-26 13:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-25 10:06 response time when querying via JDBC and via psql differs Pavel Rotek <pavel.rotek@gmail.com>
2008-02-25 11:10 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-25 14:11 ` Nikolas Everett <nik9000@gmail.com>
2008-02-26 13:26 ` valgog <valgog@gmail.com>
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