Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dxp7U-0002DR-2O for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 06:49:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dxp7T-0008J0-LT for pgsql-performance@arkaria.postgresql.org; Fri, 29 Sep 2017 06:49:55 +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 1dxp7S-0008Dr-FK for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 06:49:54 +0000 Received: from mail-qt0-x235.google.com ([2607:f8b0:400d:c0d::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dxp7P-0004DG-Bm for pgsql-performance@postgresql.org; Fri, 29 Sep 2017 06:49:53 +0000 Received: by mail-qt0-x235.google.com with SMTP id i13so456164qtc.11 for ; Thu, 28 Sep 2017 23:49:50 -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=zDDmRXytyAhEJfcSY9WMmpilO6/PIoZiwzfgvppcb68=; b=SfbA3pxglFVk9pF6YLRGkGCAQthj0qnURGKywLA56eqQacYzxVChnxrnzlkN3RLBWV DV1rSl7iT/bAswdXR9J0tT1vqR0P9Djvg/6T4R5R1Re4QzKC3BqtJt1y6R2PYJ2BevnK fmbCRltu3hl4JFCRW1wPF47x9q2I8E6JPJPQ42P56JX98eZ2ljJtRN1PfhYilY5U6Oe2 eiZZurVxJqYMlQV9cJ7LXGg/J0lKoKopqhAyf/vZtUmLD9DvaT++JkoIkQojDxdfMAIF V9pWPDG0AGWk+7GD6YSHm8Q8YaufGS88q2k7ngmqCC5LDEhkdIGpwFHMwHGNCZA2AvYI 04kQ== 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=zDDmRXytyAhEJfcSY9WMmpilO6/PIoZiwzfgvppcb68=; b=iR8XwCMMO3gpUvBdem9ov4FhoPn2S+Htr7VTfDvZzG9y4tnaZ2EeTbLIqKJcvaWiv8 KAU3B9n3lkDoi1u6wpmBiuhHiCPffc+V1+Iwai7sDa2P4awDlRtnyo1Y2QTmMBLp2Y1A lYyQdt13HaP0lUVcGQHMPWCZrZy/vvfT4+upCcKE4M/W5EdmacYAUGaIeuxL6bCcNXVA DSL9betvp9j7+qCC7TGBWbH+jmYSlQ9fr+6jAQDvx9yEVv+5D+2I2uSjK4qv+4xvSZI7 Axfbf/4xn1rt6TSuUqNT7quRxaEHk1HW8kY7J82CLQ3UpCokFMyfrGrPW6MTa962pQjG FMKQ== X-Gm-Message-State: AMCzsaXDt6Q6KIZEVmHivZRwQMo3yYafs4Dt+V8ZTq0hCwTM2VOGYcIr UFePKuZgwH5Ma9OwBZP52kH7+AxFBtIJsM8vcg== X-Google-Smtp-Source: AOwi7QDQLRkQiZB7Q/iBzIWyLX6KKasxC9VYglYa8zo3PsNnFx4Kn6o+ICbVefky2TLS4ALSN+fRw5ZaHCY8dIWVS3w= X-Received: by 10.200.26.65 with SMTP id q1mr4482119qtk.186.1506667789351; Thu, 28 Sep 2017 23:49:49 -0700 (PDT) MIME-Version: 1.0 Received: by 10.140.18.83 with HTTP; Thu, 28 Sep 2017 23:49:48 -0700 (PDT) In-Reply-To: References: <5F8F324242D0E14B97060D4D32CD0F5C014910CA886C@FRSPX100.fr01.awl.atosorigin.net> From: Jeff Janes Date: Thu, 28 Sep 2017 23:49:48 -0700 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="f403045d6da06100aa055a4e6ea5" 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 --f403045d6da06100aa055a4e6ea5 Content-Type: text/plain; charset="UTF-8" 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 --f403045d6da06100aa055a4e6ea5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On T= hu, Sep 28, 2017 at 2:59 AM, Subramaniam C <subramaniam31784@gmai= l.com> wrote:
First output show the ou= tput when the query is executed from sql command line. The second output sh= ow 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 (h= ealth_index) instead its doing sequence scan. Please let us know how this i= ssue 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_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 = 9;1505989186834'::bigint) AND ("timestamp" <=3D '15059= 90086834'::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=3D10056= 34 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 '150598950= 0000'::bigint) AND ("timestamp" <=3D '1505990400000= 9;::bigint))



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

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

=
Cheers,

Jeff
--f403045d6da06100aa055a4e6ea5--