Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxVcy-0006vd-8x for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 10:01:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxVcx-0005fv-S5 for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 10:01:07 +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 1dxVb7-0001fS-Ok for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 09:59:14 +0000 Received: from mail-wr0-x22b.google.com ([2a00:1450:400c:c0c::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxVb4-0003lm-BG for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 09:59:12 +0000 Received: by mail-wr0-x22b.google.com with SMTP id g29so1645475wrg.11 for ; Thu, 28 Sep 2017 02:59:10 -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=NaBUga8LtXrwwsRmRB7BMTbgoPgvC6G14HdTcqhY7KY=; b=oBkgofRBwEkYMHNyjk/iAGcxdjwtkcAaOm44+h4PksjkAT/M/oJVMQ2Nt2R6k8o2HN 0lUDYd0Yk6W3GFP2HjJ7rgcOYeUdSqr0LjWBU5VJ3SVbzpYh5abjqoBB4pEjFOAzojYb wolTlZLeaNdSwFECSVKv21eso1+ZGIyWpWxvCG/EMBEliahBafIUIGynfFuTQSXo+VIN UP/9wnWiqr+kSAW0BJJ9LW2JVz67gYDNMyLP3OPeNpzU9jyzhCuIpyJkmApHs9qRD2fl i4C2BQjuJ475vkoEMl8CWAtPojh7xc61TOPpzw83qoKedC5rxSXHsGaEj5pyVDcGKvbX oZ4A== 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=NaBUga8LtXrwwsRmRB7BMTbgoPgvC6G14HdTcqhY7KY=; b=rTYnieGXI0hRtiMJHvkVkZygl4qd5UT/HZn5GWMln9qpVpyDKHOpwG5iWpz02X8lFK W4Aq0F/rbnGG9EHBUQoT7qjUTYNAX6Auv6kxwkEEPxk5kqta8RNEFWyTBBZwhcu/YkEW Ztj8TurPfLm5psuc+RHORhjgsfD2eJ6iyId/8JDSB2h+Wj6QTLJwQJX+liI0pKRBaU0U /ahLFGkfHnr5WVO/14zHyfGvcZSZHfgCXLZe/KMMworc3SAC7Hzov2gBCqkIj9ud/i8P urtYgulFwq8tXWYruKvOg69AQ4Cv70ZQxkYpUZVObRJkowK8dvhNAibTdjMwmctmIQMr ulZg== X-Gm-Message-State: AHPjjUgTGwxDTYwlMR96SaraNb/UCqFer5vOwdgwLIBsPVOaXrOPH794 8avUin9Txba3jid7GOusSbeDa5gn/ZGx5FDfof0= X-Google-Smtp-Source: AOwi7QBVZxr/AQzt48mgCYyo9cUWyCvnaid2hNG3BVt46R9U3OMZoIXaZ1TK7dC1vFTywDwFnOuHktwwSNKtKX0uLjo= X-Received: by 10.25.228.148 with SMTP id x20mr4249lfi.11.1506592748824; Thu, 28 Sep 2017 02:59:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.235.85 with HTTP; Thu, 28 Sep 2017 02:59:08 -0700 (PDT) In-Reply-To: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> References: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> From: Subramaniam C Date: Thu, 28 Sep 2017 15:29:08 +0530 Message-ID: Subject: Re: Slow query in JDBC To: Pavy Philippe Cc: Julien Rouhaud , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c0dc5689d6bde055a3cf59e" 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 --94eb2c0dc5689d6bde055a3cf59e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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.) *Limit (cost=3D510711.53..510711.58 rows=3D20 width=3D72)* * -> Sort (cost=3D510711.53..511961.53 rows=3D500000 width=3D72)* * Sort Key: health_timeseries_table.health* * -> WindowAgg (cost=3D0.98..497406.71 rows=3D500000 width=3D72)* * -> Merge Left Join (cost=3D0.98..491156.71 rows=3D500000 width=3D64)* * Merge Cond: (object_table.uuid =3D health_timeseries_table.mobid)* * -> Unique (cost=3D0.42..57977.00 rows=3D500000 width=3D64)* * -> Index Scan Backward using object_table_pkey on object_table (cost=3D0.42..56727.00 rows=3D500000 width=3D64)* * Index Cond: (("timestamp" >=3D 0) AND ("timestamp" <=3D '1505990086834'::bigint))* * Filter: (tenantid =3D 'perspica'::text)* * -> Materialize (cost=3D0.56..426235.64 rows=3D55526 width=3D16)* * -> Unique (cost=3D0.56..425541.56 rows=3D5552= 6 width=3D24)* * -> Index Only Scan using health_index on health_timeseries_table (cost=3D0.56..421644.56 rows=3D1558800 width=3D24)= * * Index Cond: (("timestamp" >=3D '1505989186834'::bigint) AND ("timestamp" <=3D '1505990086834'::bigint))* *LOG: duration: 1971.697 ms* 2.) Limit (cost=3D457629.21..457629.26 rows=3D20 width=3D72) -> Sort (cost=3D457629.21..458879.21 rows=3D500000 width=3D72) Sort Key: health_timeseries_table.health -> WindowAgg (cost=3D367431.49..444324.39 rows=3D500000 width=3D7= 2) -> Merge Left Join (cost=3D367431.49..438074.39 rows=3D5000= 00 width=3D64) Merge Cond: (object_table.uuid =3D health_timeseries_table.mobid) -> Unique (cost=3D0.42..57977.00 rows=3D500000 width= =3D64) -> Index Scan Backward using object_table_pkey on object_table (cost=3D0.42..56727.00 rows=3D500000 width=3D64) Index Cond: (("timestamp" >=3D '0'::bigint) AND ("timestamp" <=3D '1505990400000'::bigint)) Filter: (tenantid =3D 'perspica'::text) -> Materialize (cost=3D367431.07..373153.32 rows=3D55= 526 width=3D16) -> Unique (cost=3D367431.07..372459.24 rows=3D5= 5526 width=3D24) -> Sort (cost=3D367431.07..369945.16 rows=3D1005634 width=3D24) Sort Key: health_timeseries_table.mobid DESC, health_timeseries_table."timestamp" DESC, health_timeseries_table.health -> Seq Scan on health_timeseries_table (cost=3D0.00..267171.00 rows=3D1005634 width=3D24) Filter: (("timestamp" >=3D '1505989500000'::bigint) AND ("timestamp" <=3D '1505990400000'::bigint)) On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe wrote: > https://www.postgresql.org/docs/current/static/auto-explain.html > > > -----Message d'origine----- > De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] De la part de Julien Rouhaud > Envoy=C3=A9 : jeudi 28 septembre 2017 11:21 > =C3=80 : Subramaniam C > Cc : pgsql-performance@postgresql.org > Objet : Re: [PERFORM] Slow query in JDBC > > On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < > subramaniam31784@gmail.com> wrote: > > I configured cursor_tuple_fraction to 1 but still I am facing the same > > issue. > > Can you show explain (analyze, buffers) of the query when run from psql > and run from application (you can use auto_explain for that if needed, se= e > https://www.postgresql.org/docs/current/static/auto-explain.html). > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > !!!********************************************************* > **************************** > "Ce message et les pi=C3=A8ces jointes sont confidentiels et r=C3=A9serv= =C3=A9s =C3=A0 l'usage > exclusif de ses destinataires. Il peut =C3=A9galement =C3=AAtre prot=C3= =A9g=C3=A9 par le secret > professionnel. Si vous recevez ce message par erreur, merci d'en avertir > imm=C3=A9diatement l'exp=C3=A9diteur et de le d=C3=A9truire. L'int=C3=A9g= rit=C3=A9 du message ne > pouvant =C3=AAtre assur=C3=A9e sur Internet, la responsabilit=C3=A9 de Wo= rldline ne pourra > =C3=AAtre recherch=C3=A9e quant au contenu de ce message. Bien que les me= illeurs > efforts soient faits pour maintenir cette transmission exempte de tout > virus, l'exp=C3=A9diteur ne donne aucune garantie =C3=A0 cet =C3=A9gard e= t sa > responsabilit=C3=A9 ne saurait =C3=AAtre recherch=C3=A9e pour tout dommag= e r=C3=A9sultant d'un > virus transmis. > > This e-mail and the documents attached are confidential and intended > solely for the addressee; it may also be privileged. If you receive this > e-mail in error, please notify the sender immediately and destroy it. As > its integrity cannot be secured on the Internet, the Worldline liability > cannot be triggered for the message content. Although the sender endeavou= rs > to maintain a computer virus-free network, the sender does not warrant th= at > this transmission is virus-free and will not be liable for any damages > resulting from any virus transmitted.!!!" > --94eb2c0dc5689d6bde055a3cf59e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
First outpu= t show the output when the query is executed from sql command line. The sec= ond 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 kn= ow how this issue can be resolved from JDBC?

1.)


Limit=C2=A0 (cost= =3D510711.53..510711.58 rows=3D20 width=3D72)

=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D510711.53..511961.= 53 rows=3D500000 width=3D72)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: health_timeseries_table.health<= /span>

=C2=A0 =C2=A0 =C2=A0 =C2=A0 -&g= t;=C2=A0 WindowAgg=C2=A0 (cost=3D0.98..497406.71 rows=3D500000 width=3D72)<= /i>

=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Merge Left Join=C2=A0 (cost=3D0.98..49115= 6.71 rows=3D500000 width=3D64)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = Merge Cond: (object_table.uuid =3D health_timeseries_table.mobid)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Unique=C2=A0 (cost=3D0.42..5= 7977.00 rows=3D500000 width=3D64)

= =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 Scan Backward using object_table= _pkey on object_table=C2=A0 (cost=3D0.42..56727.00 rows=3D500000 width=3D64= )

=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 0) AND ("tim= estamp" <=3D '1505990086834'::bigint))

<= span class=3D"gmail-m_-8660843490239021530gmail-Apple-tab-span" style=3D"wh= ite-space:pre-wrap"> =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: = (tenantid =3D 'perspica'::text)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 ->=C2=A0 Materialize=C2=A0 (cost=3D0.56..426235.64 rows=3D55526 = width=3D16)

=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 Unique=C2=A0 (cost=3D0.56..425541.56 rows=3D55526 width=3D2= 4)

=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_timeseries_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 '1505989186834'::bigint) AND ("timestamp" = <=3D '1505990086834'::bigint))

<= span style=3D"font-variant-ligatures:no-common-ligatures">LOG:=C2=A0 dur= ation: 1971.697 ms





2.)<= /p>


Limit=C2=A0 (cost=3D457629.21..457629.26 rows= =3D20 width=3D72)

=C2=A0 ->=C2=A0 Sort= =C2=A0 (cost=3D457629.21..458879.21 rows=3D500000 width=3D72)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: health_timeseri= es_table.health

=C2=A0 =C2=A0 =C2=A0 =C2= =A0 ->=C2=A0 WindowAgg=C2=A0 (cost=3D367431.49..444324.39 rows=3D500000 = width=3D72)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Merge Left Join=C2=A0 (cost=3D367431.49..4= 38074.39 rows=3D500000 width=3D64)

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Merge Cond: = (object_table.uuid =3D health_timeseries_table.mobid)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 ->=C2=A0 Unique=C2=A0 (cost=3D0.42..57977.00 rows=3D500000 width= =3D64)

=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 S= can Backward using object_table_pkey on object_table=C2=A0 (cost=3D0.42..56= 727.00 rows=3D500000 width=3D64)

=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= '0'::bigint) AND ("timestamp" <=3D '1505990400000= '::bigint))

=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: (tenantid =3D 'perspica'::text)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ->=C2=A0 Materialize=C2=A0 (cost=3D367431.07..373153.3= 2 rows=3D55526 width=3D16)

=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 Unique=C2=A0 (cost=3D367431.07..372459.24 rows=3D55526 widt= h=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 Sort=C2=A0 (cost=3D367431.07..369945.16 rows=3D1005634 widt= h=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 Sort Key: health_timeseries_table.mobid DESC, heal= th_timeseries_table."timestamp" DESC, health_timeseries_tabl= e.health

=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_table= =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 Filte= r: (("timestamp" >=3D '1505989500000'::bigint) AND (&q= uot;timestamp" <=3D '1505990400000'::bigint))


On Thu, Sep 28,= 2017 at 2:56 PM, Pavy Philippe <Philippe.Pavy@worldline.com= > wrote:
https://www.postgresql.org/docs/current/static/auto-<= wbr>explain.html


-----Message d'origine-----
De : pgsql-perfor= mance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]= De la part de Julien Rouhaud
Envoy=C3=A9 : jeudi 28 septembre 2017 11:21
=C3=80 : Subramaniam C
Cc : pgsql-performance@= postgresql.org
Objet : Re: [PERFORM] Slow query in JDBC

On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <subramaniam31784@gmail.com> wrote:
> I configured cursor_tuple_fraction to 1 but still I am facing the same=
> issue.

Can you show explain (analyze, buffers) of the query when run from psql and= run from application (you can use auto_explain for that if needed, see https://www.postgresql.org/docs/cu= rrent/static/auto-explain.html).


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance

!!!**************************************************************= ***********************
"Ce message et les pi=C3=A8ces jointes sont confidentiels et r=C3=A9se= rv=C3=A9s =C3=A0 l'usage exclusif de ses destinataires. Il peut =C3=A9g= alement =C3=AAtre prot=C3=A9g=C3=A9 par le secret professionnel. Si vous re= cevez ce message par erreur, merci d'en avertir imm=C3=A9diatement l= 9;exp=C3=A9diteur et de le d=C3=A9truire. L'int=C3=A9grit=C3=A9 du mess= age ne pouvant =C3=AAtre assur=C3=A9e sur Internet, la responsabilit=C3=A9 = de Worldline ne pourra =C3=AAtre recherch=C3=A9e quant au contenu de ce mes= sage. Bien que les meilleurs efforts soient faits pour maintenir cette tran= smission exempte de tout virus, l'exp=C3=A9diteur ne donne aucune garan= tie =C3=A0 cet =C3=A9gard et sa responsabilit=C3=A9 ne saurait =C3=AAtre re= cherch=C3=A9e pour tout dommage r=C3=A9sultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely= for the addressee; it may also be privileged. If you receive this e-mail i= n error, please notify the sender immediately and destroy it. As its integr= ity cannot be secured on the Internet, the Worldline liability cannot be tr= iggered for the message content. Although the sender endeavours to maintain= a computer virus-free network, the sender does not warrant that this trans= mission is virus-free and will not be liable for any damages resulting from= any virus transmitted.!!!"

--94eb2c0dc5689d6bde055a3cf59e--