Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6DD422E004E for ; Mon, 25 Feb 2008 07:17:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02917-05 for ; Mon, 25 Feb 2008 07:17:13 -0400 (AST) X-Greylist: delayed 00:06:43.624326 by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.186]) by postgresql.org (Postfix) with ESMTP id 7813E2E004D for ; Mon, 25 Feb 2008 07:17:18 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id g13so1094080rvb.43 for ; Mon, 25 Feb 2008 03:17:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=2/vyKvV5bBv5WXZwGuTlipZP8zZZhDj9j6I4ALDMVF8=; b=GhAz/fWudobyCVodvPCCKEG4CIQxA84NNFJm9768Rxg8bmIuVucT+oJp/oU3WKq0awMHzVKaWM4yVt6iSd8n++0wNEdAy7xzqYT+EOutFjiJyh3LDLP4RTYNfkwvfkN07XVDNtuzGt1Cj9/JL+zRVVCR8b9HIcFvtUVM3EV4OZU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ZrBW7bEG59D7SKE52Hj511t6FOyu9QhfNUaJ9WA6TPspt67QiV0D4kTV1cUCKfpgdaRHNYR1BeXTx737EBHe67ZWh1w3YlOHYmRXteS9VOyHfswOGZmGLHXbg5eiLbo9xFAfUBJrTYEHZ2Z6/sc0fDtotgmbaRYgsynSYeesh9o= Received: by 10.141.50.17 with SMTP id c17mr1888798rvk.271.1203937832744; Mon, 25 Feb 2008 03:10:32 -0800 (PST) Received: by 10.141.128.7 with HTTP; Mon, 25 Feb 2008 03:10:32 -0800 (PST) Message-ID: <684362e10802250310m48c45cb3lad3e70312ef4b3b6@mail.gmail.com> Date: Mon, 25 Feb 2008 17:10:32 +0600 From: "Markus Bertheau" To: "Pavel Rotek" Subject: Re: response time when querying via JDBC and via psql differs Cc: pgsql-performance@postgresql.org In-Reply-To: <3556c08c0802250206w1668aaday51dc9be9fddd7b70@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3556c08c0802250206w1668aaday51dc9be9fddd7b70@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/316 X-Sequence-Number: 28858 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