Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 02D9A2E0045 for ; Mon, 25 Feb 2008 10:11:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74136-10 for ; Mon, 25 Feb 2008 10:11:31 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.248]) by postgresql.org (Postfix) with ESMTP id BB5172E004D for ; Mon, 25 Feb 2008 10:11:31 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 5so4618302agb.7 for ; Mon, 25 Feb 2008 06:11:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=9BwZLYSFTg5vuCgm6MjaXQcJxXsgSrhJe/ykYrsx8Wo=; b=IdX7SXt8j/2ssNRxPrgROlI9Hg74UTDw5/Hg2C43B945hUhZ7WC3qukN4JYLoVmNNvDi7iiO935odfn/qAhyjAMQNBmBgM2Xe16ZE6P0oIBLg9PwPLJRCd2cnM72wxZwyn6U6xKRpeUwxFjLroI2VHLd97dm4kTm3fkMxTNGyUo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=XqqyToOoaGCUnNXdXfxm+ScHMMUUScX6VyOEjxI2iY0jCqElmKrZ+pJDClcjtOqglWwRZ8BUt1kcE7NHyO3xA3nfTL5HUV+xnPl6Zjst7T1QaD8LxpVIAoHu6F4yTE8PyfHiVVXd3yAKQUkcRSn11OXkbcdS0TENlZy9nu8IipE= Received: by 10.115.109.1 with SMTP id l1mr3592912wam.136.1203948689502; Mon, 25 Feb 2008 06:11:29 -0800 (PST) Received: by 10.114.38.1 with HTTP; Mon, 25 Feb 2008 06:11:29 -0800 (PST) Message-ID: Date: Mon, 25 Feb 2008 09:11:29 -0500 From: "Nikolas Everett" To: "Markus Bertheau" Subject: Re: response time when querying via JDBC and via psql differs Cc: "Pavel Rotek" , pgsql-performance@postgresql.org In-Reply-To: <684362e10802250310m48c45cb3lad3e70312ef4b3b6@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13967_30555114.1203948689495" References: <3556c08c0802250206w1668aaday51dc9be9fddd7b70@mail.gmail.com> <684362e10802250310m48c45cb3lad3e70312ef4b3b6@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL=0.158, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/320 X-Sequence-Number: 28862 ------=_Part_13967_30555114.1203948689495 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 : > > 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 > ------=_Part_13967_30555114.1203948689495 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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

------=_Part_13967_30555114.1203948689495--