Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxUVW-00026a-Vl for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 08:49:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxUVW-00031F-0E for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 08:49:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dxUVT-0002zs-NM for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 08:49:19 +0000 Received: from mail-io0-x22f.google.com ([2607:f8b0:4001:c06::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxUVP-0004MA-Vz for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 08:49:18 +0000 Received: by mail-io0-x22f.google.com with SMTP id k101so954427iod.0 for ; Thu, 28 Sep 2017 01:49:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=Pfedro9gcrL4OLWf1XLYoNAFCy1hM+QHQxGGCDU7XPs=; b=BOC4Y5Xql2s7rzy9cb5/nXotiRlf0cIV6YuJ+84Q/zvjUiTqRfQLjaoVF9byamDGIN 9ANq8hq64B+RlbXuX4kEBSOYVqwJR+KR2TsuRVXkG9LZTiyBJdNOmian8vtPhjD2xPJl S43B1mzm1gcnDpi0f+1+KQod7afeyyFF6Mi8twJE5B/zwDlm95lZtZa43IpmPA0rETui FWW/hudbACJMMwVSlrU1djFkqB7QpPNbbEEfBUYM3TlOUvEoBijrEHDh6m9nVpuv+GFg oIcqYutFc8fkWSMtEHHc4nKWjCQwKCLh4t1Xe/chdtSEfcoQSO8d7tqL7KF/dHhPm/8y gCVw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=Pfedro9gcrL4OLWf1XLYoNAFCy1hM+QHQxGGCDU7XPs=; b=ThuZkzLfJLi4HqHosjMZwcqxQqkG68RPAW8JUC4Q5YiJd/BvDa1nB0Cwy+WNemOU+5 4bOnFAT7WYke3UNkS/RhD7MA6OcC4JeRnvDp8Dh/JuKIoIYQPGqk8H4WI8+DGBV6B6gs QJUBhxPIYW/ANzJJeN3IbgEutZJNAY04Cg9o4GKKrAFxPfn7ZyzXdb5OMnXGJR/uvnjM tbq5lnUJhPAY2lRPtUBm7jTizoHWJFnJER46QPOkeOeQTRDLSsT3YEfjAYJ6XjGp8TmE /cTuw6fw+l3+6LBlCLfVsdFa38dqnhzldtqaBBzybbuy6poQ4USxUzEfVXkf273T/d1T w+pA== X-Gm-Message-State: AMCzsaWM6FXdVqYIPWLGNJmA+1s9cA/fdJCWfIFnP9zYUaeC3r2lmNEA FTrSv4l01xuz1weHpawjgLoLCgl3vCKJ/yXwi40= X-Google-Smtp-Source: AOwi7QAWkZkjrzM5vB08FR4K9ClZOaYB2U58aMg9stdSI2aWxHN4dBtb5x1Y6XbF2zRoB1zluiWGqECpc4Lo7nHHqGs= X-Received: by 10.107.88.17 with SMTP id m17mr6001863iob.101.1506588553868; Thu, 28 Sep 2017 01:49:13 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.162.205 with HTTP; Thu, 28 Sep 2017 01:48:53 -0700 (PDT) In-Reply-To: References: From: Julien Rouhaud Date: Thu, 28 Sep 2017 10:48:53 +0200 Message-ID: Subject: Re: Slow query in JDBC To: Subramaniam C Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset="UTF-8" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C wrote: > Hi > > When I try to execute the query from sql command line then that query is > taking only around 1 sec. But when I execute the query using JDBC(Java) > using preparedStatement then the same query is taking around 10 secs. > > Can you please let us know the reason and how to fix this issue? I think jdbc always uses cursor, which can be problematic with default configuration, because postgres will try to generate plans that returns fast the first rows but not all the rows . Can you try to configure cursor_tuple_fraction to 1 and see if that fixes your issue? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance