Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxe7A-0000SJ-Q7 for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 19:04:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxe7A-0005rH-9v for pgsql-performance@arkaria.postgresql.org; Thu, 28 Sep 2017 19:04:52 +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 1dxe78-0005kq-4C for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 19:04:50 +0000 Received: from mail-it0-x241.google.com ([2607:f8b0:4001:c0b::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxe72-0000l7-GH for pgsql-performance@postgresql.org; Thu, 28 Sep 2017 19:04:49 +0000 Received: by mail-it0-x241.google.com with SMTP id 4so2428597itv.4 for ; Thu, 28 Sep 2017 12:04:43 -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=qCou/BS0AUNokmzwTWVxnfQcS9o8ZRrfSRO4m61Gi7s=; b=tw8chzA47KXBMQhb28FOttMzaZSLIIu7ZuRaRil8xGj343My2/9iJSvQj767VRbbmq wIWAN1IjahVCymW+oSopRHk/hl5N0iGDZy7HKEN7+AUZe9rFFGQVYkWgtk29gRAxeZ7B jow273uOBfZWI+UtFxph+90lhdTMBNLNgA0mFLrj67Sbf9S946mTK57vwTW2+iqzfDVb oastipFga510Hr+h9EjIiXxg0CSM7LP4w7gVqJ7ZM7+UUNYX/1/tPvYoH1WsV3dUNim8 o/3Kpx5TihNnz0xks3/AXgZw+Fcjaoa7Kmed55vOtns33tMLteZ8zbU4W1t3P1LNJC1Q fYkg== 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=qCou/BS0AUNokmzwTWVxnfQcS9o8ZRrfSRO4m61Gi7s=; b=kf61zEeO2dr924hPIeeCV4yVq+za7LR+WniyYQCfDj58W4l9OhMf61qzmGu4DPoObD OZOLwqR/PPf8jLlSFJJUWYm2aMcKC9WItSDPkZPu7Jjda2Adoyi+98T1RZUt7MAxN3QH jcbx9ieIvuaNnkVpkU0TPTbTZw/m2n5eLDci2X/EOhiImSNqfKcEtQbdKRlrdaZTWKy8 MPuBj8KjnPTRNYM2e/89KwJrFDN4jsjYUYc7umBEPx5pi7H3xPvI1QsqfADedMHDsnZt VgzOZSMEJOo/PJ689bKFPK3QXX/ol8CBKyV3gbZsupnUYePcLZc3UUQeHI/b/I0VLQbj HS/A== X-Gm-Message-State: AMCzsaXxWUP3SopG5iOoD17rpXfAT6UBv7l/M2nvkgGTHiwIh226QWk8 EGZUVDrn++5e60JX4SP62BAs9eJyW709Ux/cyVM= X-Google-Smtp-Source: AOwi7QAURHKkwYR0K589lW6yzvT20/L3A5725LRk478R+f6YEuOdq10IdhRMU42ppxces+aQF3su8waJJLS6Mvzhqow= X-Received: by 10.36.54.74 with SMTP id l71mr2408352itl.37.1506625481357; Thu, 28 Sep 2017 12:04:41 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.57.86 with HTTP; Thu, 28 Sep 2017 12:04:20 -0700 (PDT) In-Reply-To: References: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> From: Dave Cramer Date: Thu, 28 Sep 2017 15:04:20 -0400 X-Google-Sender-Auth: EJ_2h2mBWTkBKqqCGGh-dWSFgJI Message-ID: Subject: Re: Slow query in JDBC To: Subramaniam C Cc: Pavy Philippe , Julien Rouhaud , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a1144d1b8a040d8055a449465" 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 --001a1144d1b8a040d8055a449465 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Why are you using such an old version of the driver ? Either way the driver is going to use prepare statement to run this, that is the difference from it an psql. If you want to see the explain in psql you will need to do prepare foo as then explain execute foo; FWIW upgrading the driver won't help this situation but there's still no reason not to upgrade. Dave Cramer davec@postgresintl.com www.postgresintl.com On 28 September 2017 at 12:32, Subramaniam C wrote: > The JDBC version is 9.4-1201-jdbc41. > > Query :- > > select count(*) OVER() AS count,uuid,availability, > objectname,datasourcename,datasourcetype,objecttype,health from (select > distinct on (health_timeseries_table.mobid) mobid, > health_timeseries_table.health, health_timeseries_table.timestamp from > health_timeseries_table where timestamp >=3D 1505989186834 and timestamp = <=3D > 1505990086834 ORDER BY health_timeseries_table.mobid DESC, > health_timeseries_table.timestamp DESC, health_timeseries_table.health > ASC) t right join (SELECT DISTINCT ON (object_table.uuid) uuid, > object_table.timestamp,object_table.availability,object_ > table.objectname,object_table.datasourcename,object_table. > datasourcetype,object_table.objecttype FROM object_table where > object_table.timestamp >=3D 0 and object_table.timestamp <=3D 15059900868= 34 and > object_table.tenantid =3D 'perspica' ORDER BY object_table.uuid DESC, > object_table.timestamp DESC)u on (t.mobid =3D u.uuid) order by health asc > limit 20 offset 0; > > > Please let us know any other details? > > > Thanks and Regards > > Subramaniam > > On Thu, Sep 28, 2017 at 7:29 PM, Dave Cramer wrote: > >> What version of the driver are you using? >> >> The driver does not automatically use a cursor, but it does use prepared >> statements which can be slower. >> >> >> Can you provide the query and the jdbc query ? >> >> >> >> Dave Cramer >> >> davec@postgresintl.com >> www.postgresintl.com >> >> On 28 September 2017 at 05:59, Subramaniam C >> wrote: >> >>> First output show the output when the query is executed from sql comman= d >>> 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=3D= 72)* >>> >>> * -> Merge Left Join (cost=3D0.98..491156.71 rows=3D500= 000 >>> 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'::tex= t)* >>> >>> * -> Materialize (cost=3D0.56..426235.64 rows=3D5= 5526 >>> width=3D16)* >>> >>> * -> Unique (cost=3D0.56..425541.56 rows=3D= 55526 >>> 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= =3D72) >>> >>> -> Merge Left Join (cost=3D367431.49..438074.39 >>> 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'::bigint) AND ("timestamp" <=3D '1505990400000'::bigint)) >>> >>> Filter: (tenantid =3D 'perspica'::text) >>> >>> -> Materialize (cost=3D367431.07..373153.32 >>> rows=3D55526 width=3D16) >>> >>> -> Unique (cost=3D367431.07..372459.24 >>> rows=3D55526 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 < >>> Philippe.Pavy@worldline.com> 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 sa= me >>>> > issue. >>>> >>>> Can you show explain (analyze, buffers) of the query when run from psq= l >>>> and run from application (you can use auto_explain for that if needed,= see >>>> 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=A9se= rv=C3=A9s =C3=A0 >>>> l'usage exclusif de ses destinataires. Il peut =C3=A9galement =C3=AAtr= e 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=A9grit=C3=A9 du >>>> message ne pouvant =C3=AAtre assur=C3=A9e sur Internet, la responsabil= it=C3=A9 de >>>> Worldline ne pourra =C3=AAtre recherch=C3=A9e quant au contenu de ce m= essage. Bien >>>> que les meilleurs efforts soient faits pour maintenir cette transmissi= on >>>> exempte de tout virus, l'exp=C3=A9diteur ne donne aucune garantie =C3= =A0 cet =C3=A9gard et >>>> sa responsabilit=C3=A9 ne saurait =C3=AAtre recherch=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 th= is >>>> e-mail in error, please notify the sender immediately and destroy it. = As >>>> its integrity cannot be secured on the Internet, the Worldline liabili= ty >>>> cannot be triggered for the message content. Although the sender endea= vours >>>> to maintain a computer virus-free network, the sender does not warrant= that >>>> this transmission is virus-free and will not be liable for any damages >>>> resulting from any virus transmitted.!!!" >>>> >>> >>> >> > --001a1144d1b8a040d8055a449465 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Why are you using such an old version of the driver ?
=
Either way the driver is going to use prepare statement to r= un this, that is the difference from it an psql.

<= br>
If you want to see the explain in psql you will need to do=C2= =A0

prepare foo as <your query>
then explain execute foo;

FWIW upgrad= ing the driver won't help this situation but there's still no reaso= n not to upgrade.

<= div>

On 28 September 2017 at 12:32, Subramaniam C= <subramaniam31784@gmail.com> wrote:
The JDBC version is=C2=A09.4-1201-jdbc41.

Query :-

select= count(*) OVER() AS count,uuid,availability,objectname,datasourcename,= datasourcetype,objecttype,health from (select distinct on (health= _timeseries_table.mobid) mobid, health_timeseries_table.health, h= ealth_timeseries_table.timestamp from health_timeseries_table where ti= mestamp >=3D 1505989186834 and timestamp <=3D 1505990086834 ORDER BY = health_timeseries_table.mobid DESC, health_timeseries_table.timestamp = DESC, health_timeseries_table.health ASC) t right join (SELECT DISTINCT ON = (object_table.uuid) uuid, object_table.timestamp,object_table.availabi= lity,object_table.objectname,object_table.datasourcename,object_t= able.datasourcetype,object_table.objecttype FROM object_table whe= re=C2=A0 object_table.timestamp >=3D 0 and object_table.timestamp <= =3D 1505990086834 and object_table.tenantid =3D 'perspica' ORDER BY= object_table.uuid DESC, object_table.timestamp DESC)u on (t.mobid =3D u.uu= id) order by health asc limit 20 offset 0;


Ple= ase let us know any other details?


Thanks and= Regards

Subram= aniam


On Thu, S= ep 28, 2017 at 7:29 PM, Dave Cramer <pg@fastcrypt.com> wrote:=
What version of the dri= ver are you using?

The driver does not automatically use= a cursor, but it does use prepared statements which can be slower.


Can you provide the query and the jdbc que= ry ?



On 28 September 2017 at 05:59, Subramaniam C= <subramaniam31784@gmail.com> wrote:
First output show the output when the query is executed from sql c= ommand line. The second output show when it is executed from the applicatio= n. AS per the output it is clear that the when the query is executed throug= h JDBC its not using the index (health_index) instead its doing sequence sc= an. Please let us know how this issue can be resolved from JDBC?

1.)


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

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

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 Sort Key: health_timeseries_table.health

=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 WindowAgg=C2= =A0 (cost=3D0.98..497406.71 rows=3D500000 width=3D72)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 M= erge Left Join=C2=A0 (cost=3D0.98..491156.71 rows=3D500000 width=3D64)<= /span>

=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_timeseri= es_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 (cos= t=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 Scan Backward using object_table_pke= y 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 Inde= x Cond: (("timestamp" >=3D 0) AND ("timestamp" <= =3D '1505990086834'::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= =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=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 Index Only Scan using=C2=A0health_index=C2=A0on health_timeseries_table=C2=A0 (cost=3D0.56..421644.56 rows=3D1558= 800 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 = 9;1505990086834'::bigint))

LOG:=C2=A0 duration: 197= 1.697 ms





2.)


Limit=C2=A0 (cost=3D457629.21..457629.26 rows=3D20 wid= th=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_timeseries_table.health

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 WindowAgg=C2=A0 (cost=3D367431.49..444= 324.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=3D36743= 1.49..438074.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 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: ((&= quot;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.32 r= ows=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 Uni= que=C2=A0 (cost=3D367431.07..372459.24 rows=3D55526 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 Sort=C2=A0 (cost= =3D367431.07..369945.16 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 Sort Key: health_ti= meseries_table.mobid DESC, health_timeseries_table."timestamp&quo= t; DESC, health_timeseries_table.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 healt= h_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 F= ilter: (("timestamp" >=3D '1505989500000'::bigint) AND= ("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-explain.html

-----Message d'origine-----
De : pgsql-performance-owner@postgresql.org [mailto:pgsql-perfor= mance-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.!!!"




--001a1144d1b8a040d8055a449465--