Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxso1-0008OA-B5 for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 10:46:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxso0-0002Wl-90 for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 10:46:04 +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 1dxsmE-0007lr-3v for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 10:44:14 +0000 Received: from mail-wr0-x230.google.com ([2a00:1450:400c:c0c::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxsm6-0005dl-Vg for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 10:44:13 +0000 Received: by mail-wr0-x230.google.com with SMTP id b21so1703791wrg.7 for ; Fri, 29 Sep 2017 03:44:06 -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=HXACBBEikahEKeZDwEYEfMumzmiup66oTvPHLK1CxpU=; b=JHpBeHAmVnA1t9O0dBkytyoKCXHAjoq3Kny+SlC2w6pQnR2Dz/xaa2USU4hB+R2Nzd ZoiWaZDz6DI1YxAdiNOK5FkMUWuSoSSgEkm7VjEkMYkFlGQG3KLg8P3QyCS2lQUGn7iv MI/rVwgJksxtfR8lzlRl6zRnCO+wUDPgQOus29uHT2c2fVx+Umt2ictH2nte9KvtXRze UqKo3KxuZcP01+Vk4E/T2HtDroNtxf3HQ1D+c42upOKQG/p1ncTs57CkbGxS1LOm6nyM WIubtoYtrgGiBRzoa+dxJpDmMYdNDVi3GAdGLGmPiBLI7+yQRVMBxowPZo8CevewdlHt 4muw== 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=HXACBBEikahEKeZDwEYEfMumzmiup66oTvPHLK1CxpU=; b=CADnhYOJInL8shofbJgHiuYMZZ9bmqZKRAnTQEo3ZCu/jzn+cbH80D+grt1S2uElAG iNka9s+zlg8i4mHkVYFW7/V+FiCmzCDzoMUSf4qoxbiQlxZLEhkWB+r1ALvZwO8CHGDx beGr4SsO/HT6ne5OALfqOT8YLveezPLPrUeWye4A0K39F9gPYy3BE2kTRdf39R1BloBi GeukwgbEGrsZaIJdmdxh4GMMAMw6SenuWpDqMM7NoF09eoVZSaM7LFInVKa7vPooueo6 UqXS0fVOkNcqNFYN7bgSKQ/cjKnxjaNLBFjWW8rKkLmZDhCdJv5/psNqdyM3QC39WmSh c+/g== X-Gm-Message-State: AHPjjUj9/NBE7hbb6dIgEm4CT1UaTIIVKh1rhT3bIRpHHquw+L6mrN4B pM6tPNkiEr2UqKetlZroQHehjeYbDXlYw2brXXI= X-Google-Smtp-Source: AOwi7QBBbbA8y5D3DiwfpPd7zXuRWDP8DiNujt9pbTnzF7FMHYUhv/4ylTbqlq6m3B0JPP4E6lIaEeodl3mK5d7eR7M= X-Received: by 10.46.67.29 with SMTP id q29mr3276312lja.85.1506681843959; Fri, 29 Sep 2017 03:44:03 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.235.85 with HTTP; Fri, 29 Sep 2017 03:44:03 -0700 (PDT) In-Reply-To: References: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> From: Subramaniam C Date: Fri, 29 Sep 2017 16:14:03 +0530 Message-ID: Subject: Re: Slow query in JDBC To: Jeff Janes Cc: Pavy Philippe , Julien Rouhaud , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c075aec194dcf055a51b4f2" 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 --94eb2c075aec194dcf055a51b4f2 Content-Type: text/plain; charset="UTF-8" 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 > 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 > --94eb2c075aec194dcf055a51b4f2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes you are right the timestamp which the application was = providing was in seconds whereas the query which was using index had a time= stamp in milliseconds. So the query was taking time in application.

On Fri, Sep 29, 201= 7 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> wrot= e:
First output show the out= put when the query is executed from sql command line. The second output sho= w when it is executed from the application. AS per the output it is clear t= hat the when the query is executed through JDBC its not using the index (he= alth_index) instead its doing sequence scan. Please let us know how this is= sue 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_i= ndex=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 '15059= 89186834'::bigint) AND ("timestamp" <=3D '150599008683= 4'::bigint))

=C2=A0
=

2.)

=


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



Th= ose are different queries, so it is not terribly surprising it might choose= a different plan.

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

Cheers,

Jeff

--94eb2c075aec194dcf055a51b4f2--