Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Jeff Janes
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 usin

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Subramaniam C
If I run the below commands from psql command line then in the explain output it showing as its using the index. prepare foo as explain execute foo; But if I run the same query from my application using JDBC PreparedStatement then it showing as its doing sequence scan. To which version should I

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Dave Cramer
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

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Subramaniam C
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_

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Dave Cramer
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 da...@postgresintl.com www.postgresintl.com On 28 September 2017 at 05:59, Subramania

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Subramaniam C
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. Plea

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Pavy Philippe
https://www.postgresql.org/docs/current/static/auto-explain.html -Message d'origine- De : pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] De la part de Julien Rouhaud Envoyé : jeudi 28 septembre 2017 11:21 À : Subramaniam C Cc : pgsql-performance@po

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Julien Rouhaud
On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C 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.p

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Subramaniam C
I configured cursor_tuple_fraction to 1 but still I am facing the same issue. Please help. On Thu, Sep 28, 2017 at 2:18 PM, Julien Rouhaud wrote: > On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C > wrote: > > Hi > > > > When I try to execute the query from sql command line then that query is >

Re: [PERFORM] Slow query in JDBC

2017-09-28 Thread Julien Rouhaud
On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C wrote: > Hi > > When I try to execute the query from sql command line then that query is > taking only around 1 sec. But when I execute the query using JDBC(Java) > using preparedStatement then the same query is taking around 10 secs. > > Can you ple

[PERFORM] Slow query in JDBC

2017-09-28 Thread Subramaniam C
Hi When I try to execute the query from sql command line then that query is taking only around 1 sec. But when I execute the query using JDBC(Java) using preparedStatement then the same query is taking around 10 secs. Can you please let us know the reason and how to fix this issue? Thanks and Re