Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxv0g-0000e6-PO for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 13:07:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxv0g-0008QD-C7 for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 13:07:18 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dxuyi-0001JT-K4 for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 13:05:16 +0000 Received: from mail-io0-x230.google.com ([2607:f8b0:4001:c06::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxuyb-0006Ud-0l for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 13:05:15 +0000 Received: by mail-io0-x230.google.com with SMTP id d16so1379618ioj.3 for ; Fri, 29 Sep 2017 06:05:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:cc; bh=i168w4nvcq4DsnWmh6KfuvJjc5nX69nZ9DcFlDG5b/4=; b=a+AxQ6nFd6G/A0kDlAUTI+uC9S84uEV7WrXJK5XNm+yJXeUdW83vxt7Vu0OniiAkZu Wau1XD/QSKiXDaoVNJ5872NLPx/ofKABX9Gc5sbtYPXsirJMd5lUm2YhDLibPlOR/Ut8 Z99QwVz+ymRhqaIdbj6i67nMV8dhifRzL1qP3EPXj6BQdk4gLppWpMmf0e98aiiyUBNT UOcpeg0Sm3KPPPJakZi4q814V7jinsVJAI6nonRByM0QMag8VHLT0/ORwjcuSBExMLPx F7/Q3Sa8EkaqEFJspncjFn8dnTqNKP8i9709zTzEoMGq0xa/L0Qtjx9w+d3z7N8Lpesl BO0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:sender:in-reply-to:references:from :date:message-id:subject:to:cc; bh=i168w4nvcq4DsnWmh6KfuvJjc5nX69nZ9DcFlDG5b/4=; b=PT1gSwNLOz9IyVC0BrMa5vzo4RkP7ZxXPc1YAE8nBcPZ3Y6rQ+E9jeJ3BoQnHNvZcM 5oEQIo1hwMZZz3HdF5FVI+Qcce78o7fkpJD5qespUgcqAlG5jHfPy9kNP0E8Ih/JBmkj eOCgcqMjXA27HF8JnfmcmNvARdiEbxD7gWmkkwPC4/6xMyFxCg0TD62wdeVy4muHidZ2 uRstMf9YWU3iWqH69eqp071Ww8pu0JSXG7H4K7go6FvzAyoLtuGsH//Ev3VtyOu2BUHS FYIa4kusymQzt5l7m2Xehh+txqemFie1u7ifm2ynMWIxpMimUtYzOjfG9xHIoF/XkgBU Zw8w== X-Gm-Message-State: AHPjjUgXPlyqGh1MMzUELmSrlSGZiYX1H1zcP+uUWWB3J6ERjUnZosSr ZA+nthJgR+IdElMjqtnKgKIR999XZOnjlCbFvVQ= X-Google-Smtp-Source: AOwi7QD3W8u9fE93TF/N6QmqjveX4ll33proMUNmLw0+2djYFAp4usOrdUoJquDI3nX2HmGvbkDUw0oEdqu+yKAMqfY= X-Received: by 10.107.197.198 with SMTP id v189mr12272702iof.94.1506690307034; Fri, 29 Sep 2017 06:05:07 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.57.86 with HTTP; Fri, 29 Sep 2017 06:04:46 -0700 (PDT) In-Reply-To: References: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> From: Dave Cramer Date: Fri, 29 Sep 2017 09:04:46 -0400 X-Google-Sender-Auth: -5MEnKijpV3P4Z0QjHKEBoWEIjE Message-ID: Subject: Re: Slow query in JDBC To: Subramaniam C Cc: Jeff Janes , Pavy Philippe , Julien Rouhaud , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c18843489961e055a53acf3" 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 --94eb2c18843489961e055a53acf3 Content-Type: text/plain; charset="UTF-8" Good catch Jeff. as for which version. We always recommend the latest version. 42.1.4 Dave Cramer davec@postgresintl.com www.postgresintl.com On 29 September 2017 at 06:44, Subramaniam C wrote: > Yes you are right the timestamp which the application was providing was in > seconds whereas the query which was using index had a timestamp in > milliseconds. So the query was taking time in application. > > On Fri, Sep 29, 2017 at 12:19 PM, Jeff Janes wrote: > >> On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C < >> subramaniam31784@gmail.com> wrote: >> >>> First output show the output when the query is executed from sql command >>> line. The second output show when it is executed from the application. AS >>> per the output it is clear that the when the query is executed through JDBC >>> its not using the index (health_index) instead its doing sequence scan. >>> Please let us know how this issue can be resolved from JDBC? >>> >>> 1.) >>> >>> >>> * -> Index Only Scan >>> using health_index on health_timeseries_table (cost=0.56..421644.56 >>> rows=1558800 width=24)* >>> >>> * Index Cond: (("timestamp" >= >>> '1505989186834'::bigint) AND ("timestamp" <= '1505990086834'::bigint))* >>> >>> >> >>> 2.) >>> >>> >>> -> Seq Scan on >>> health_timeseries_table (cost=0.00..267171.00 rows=1005634 width=24) >>> >>> Filter: (("timestamp" >= >>> '1505989500000'::bigint) AND ("timestamp" <= '1505990400000'::bigint)) >>> >> >> >> Those are different queries, so it is not terribly surprising it might >> choose a different plan. >> >> For this type of comparison, you need to compare identical queries, >> including parameter. >> >> Cheers, >> >> Jeff >> > > --94eb2c18843489961e055a53acf3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Good catch Jeff.

as for which version. = We always recommend the latest version. 42.1.4


On 29 September 2017 at 06:44, Subramaniam C= <subramaniam31784@gmail.com> wrote:
Yes you are right the timestamp which = the application was providing was in seconds whereas the query which was us= ing index had a timestamp in milliseconds. So the query was taking time in = application.

On Fri, Sep 29, 2017 at 12:19 PM, = Jeff Janes <jeff.janes@gmail.com> wrote:
On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C <subramaniam31784@gmail.com> wrote:
First output show the output when the query is executed from s= ql command line. The second output show when it is executed from the applic= ation. AS per the output it is clear that the when the query is executed th= rough JDBC its not using the index (health_index) instead its doing sequenc= e scan. Please let us know how this issue can be resolved from JDBC?=

1.)=


=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->= ;=C2=A0 Index Only Scan using=C2=A0health_index=C2=A0on health_times= eries_table=C2=A0 (cost=3D0.56..421644.56 rows=3D1558800 width=3D24)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 Index Cond: (("timestamp" >=3D '150598918683= 4'::bigint) AND ("timestamp" <=3D '1505990086834':= :bigint))

=C2=A0

2.)

<= p style=3D"margin:0px;font-size:11px;line-height:normal;font-family:Menlo;c= olor:rgb(0,0,0)">

=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on health_timeseries_t= able=C2=A0 (cost=3D0.00..267171.00 rows=3D1005634 width=3D24)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (("timestamp" >= =3D '1505989500000'::bigint) AND ("timestamp" <=3D = 9;1505990400000'::bigint))


=

Those are different queries, so it is not terribl= y surprising it might choose a different plan.

For= this type of comparison, you need to compare identical queries, including = parameter.

Cheers,

Jeff


--94eb2c18843489961e055a53acf3--