Re: [PERFORM] Slow query in JDBC

[Dave Cramer]

Good catch Jeff.

as for which version. We always recommend the latest version. 42.1.4

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 29 September 2017 at 06:44, Subramaniam C 
wrote:

> 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 <
>> subramaniam31...@gmail.com> 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" >=
>>> '150598950'::bigint) AND ("timestamp" <= '150599040'::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
>>
>
>

Re: [PERFORM] Slow query in JDBC

[Subramaniam C]

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" >=
>> '150598950'::bigint) AND ("timestamp" <= '150599040'::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
>

Re: [PERFORM] Slow query in JDBC

[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 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" >=
> '150598950'::bigint) AND ("timestamp" <= '150599040'::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

Re: [PERFORM] Slow query in JDBC

[Subramaniam C]

5526
>>>> width=16)*
>>>>
>>>> *   ->  Unique  (cost=0.56..425541.56
>>>> rows=55526 width=24)*
>>>>
>>>> * ->  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))*
>>>>
>>>> *LOG:  duration: 1971.697 ms*
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> 2.)
>>>>
>>>>
>>>> Limit  (cost=457629.21..457629.26 rows=20 width=72)
>>>>
>>>>   ->  Sort  (cost=457629.21..458879.21 rows=50 width=72)
>>>>
>>>> Sort Key: health_timeseries_table.health
>>>>
>>>> ->  WindowAgg  (cost=367431.49..444324.39 rows=50 width=72)
>>>>
>>>>   ->  Merge Left Join  (cost=367431.49..438074.39
>>>> rows=50 width=64)
>>>>
>>>> Merge Cond: (object_table.uuid =
>>>> health_timeseries_table.mobid)
>>>>
>>>> ->  Unique  (cost=0.42..57977.00 rows=50
>>>> width=64)
>>>>
>>>>   ->  Index Scan Backward using
>>>> object_table_pkey on object_table  (cost=0.42..56727.00 rows=50
>>>> width=64)
>>>>
>>>>     Index Cond: (("timestamp" >=
>>>> '0'::bigint) AND ("timestamp" <= '150599040'::bigint))
>>>>
>>>> Filter: (tenantid = 'perspica'::text)
>>>>
>>>> ->  Materialize  (cost=367431.07..373153.32
>>>> rows=55526 width=16)
>>>>
>>>>   ->  Unique  (cost=367431.07..372459.24
>>>> rows=55526 width=24)
>>>>
>>>> ->  Sort  (cost=367431.07..369945.16
>>>> rows=1005634 width=24)
>>>>
>>>>   Sort Key:
>>>> health_timeseries_table.mobid DESC, health_timeseries_table."timestamp"
>>>> DESC, health_timeseries_table.health
>>>>
>>>>   ->  Seq Scan on
>>>> health_timeseries_table  (cost=0.00..267171.00 rows=1005634 width=24)
>>>>
>>>>
>>>> Filter: (("timestamp" >=
>>>> '150598950'::bigint) AND ("timestamp" <= '150599040'::bigint))
>>>>
>>>> On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe <
>>>> philippe.p...@worldline.com> wrote:
>>>>
>>>>> 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@postgresql.org
>>>>> Objet : Re: [PERFORM] Slow query in JDBC
>>>>>
>>>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
>>>>> subramaniam31...@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/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èces jointes sont confidentiels et réservés à
>>>>> l'usage exclusif de ses destinataires. Il peut également être protégé par
>>>>> le secret professionnel. Si vous recevez ce message par erreur, merci d'en
>>>>> avertir immédiatement l'expéditeur et de le détruire. L'intégrité du
>>>>> message ne pouvant être assurée sur Internet, la responsabilité de
>>>>> Worldline ne pourra être recherchée quant au contenu de ce message. Bien
>>>>> que les meilleurs efforts soient faits pour maintenir cette transmission
>>>>> exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard 
>>>>> et
>>>>> sa responsabilité ne saurait être recherchée pour tout dommage résultant
>>>>> 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 
>>>>> endeavours
>>>>> 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.!!!"
>>>>>
>>>>
>>>>
>>>
>>
>

Re: [PERFORM] Slow query in JDBC

[Dave Cramer]

alth
>>>
>>> ->  WindowAgg  (cost=367431.49..444324.39 rows=50 width=72)
>>>
>>>   ->  Merge Left Join  (cost=367431.49..438074.39
>>> rows=50 width=64)
>>>
>>> Merge Cond: (object_table.uuid =
>>> health_timeseries_table.mobid)
>>>
>>> ->  Unique  (cost=0.42..57977.00 rows=50
>>> width=64)
>>>
>>>   ->  Index Scan Backward using
>>> object_table_pkey on object_table  (cost=0.42..56727.00 rows=50
>>> width=64)
>>>
>>> Index Cond: (("timestamp" >=
>>> '0'::bigint) AND ("timestamp" <= '150599040'::bigint))
>>>
>>> Filter: (tenantid = 'perspica'::text)
>>>
>>> ->  Materialize  (cost=367431.07..373153.32
>>> rows=55526 width=16)
>>>
>>>           ->  Unique  (cost=367431.07..372459.24
>>> rows=55526 width=24)
>>>
>>> ->  Sort  (cost=367431.07..369945.16
>>> rows=1005634 width=24)
>>>
>>>   Sort Key:
>>> health_timeseries_table.mobid DESC, health_timeseries_table."timestamp"
>>> DESC, health_timeseries_table.health
>>>
>>>   ->  Seq Scan on
>>> health_timeseries_table  (cost=0.00..267171.00 rows=1005634 width=24)
>>>
>>>
>>> Filter: (("timestamp" >=
>>> '150598950'::bigint) AND ("timestamp" <= '150599040'::bigint))
>>>
>>> On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe <
>>> philippe.p...@worldline.com> wrote:
>>>
>>>> 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@postgresql.org
>>>> Objet : Re: [PERFORM] Slow query in JDBC
>>>>
>>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
>>>> subramaniam31...@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/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èces jointes sont confidentiels et réservés à
>>>> l'usage exclusif de ses destinataires. Il peut également être protégé par
>>>> le secret professionnel. Si vous recevez ce message par erreur, merci d'en
>>>> avertir immédiatement l'expéditeur et de le détruire. L'intégrité du
>>>> message ne pouvant être assurée sur Internet, la responsabilité de
>>>> Worldline ne pourra être recherchée quant au contenu de ce message. Bien
>>>> que les meilleurs efforts soient faits pour maintenir cette transmission
>>>> exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et
>>>> sa responsabilité ne saurait être recherchée pour tout dommage résultant
>>>> 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 endeavours
>>>> 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.!!!"
>>>>
>>>
>>>
>>
>

Re: [PERFORM] Slow query in JDBC

[Subramaniam C]

t;
>>   ->  Unique  (cost=367431.07..372459.24
>> rows=55526 width=24)
>>
>> ->  Sort  (cost=367431.07..369945.16
>> rows=1005634 width=24)
>>
>>   Sort Key:
>> health_timeseries_table.mobid DESC, health_timeseries_table."timestamp"
>> DESC, health_timeseries_table.health
>>
>>   ->  Seq Scan on
>> health_timeseries_table  (cost=0.00..267171.00 rows=1005634 width=24)
>>
>>
>> Filter: (("timestamp" >=
>> '150598950'::bigint) AND ("timestamp" <= '150599040'::bigint))
>>
>> On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe <
>> philippe.p...@worldline.com> wrote:
>>
>>> 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@postgresql.org
>>> Objet : Re: [PERFORM] Slow query in JDBC
>>>
>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
>>> subramaniam31...@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/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èces jointes sont confidentiels et réservés à
>>> l'usage exclusif de ses destinataires. Il peut également être protégé par
>>> le secret professionnel. Si vous recevez ce message par erreur, merci d'en
>>> avertir immédiatement l'expéditeur et de le détruire. L'intégrité du
>>> message ne pouvant être assurée sur Internet, la responsabilité de
>>> Worldline ne pourra être recherchée quant au contenu de ce message. Bien
>>> que les meilleurs efforts soient faits pour maintenir cette transmission
>>> exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et
>>> sa responsabilité ne saurait être recherchée pour tout dommage résultant
>>> 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 endeavours
>>> 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.!!!"
>>>
>>
>>
>

Re: [PERFORM] Slow query in JDBC

[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, Subramaniam C <subramaniam31...@gmail.com>
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.)
>
>
> *Limit  (cost=510711.53..510711.58 rows=20 width=72)*
>
> *   ->  Sort  (cost=510711.53..511961.53 rows=50 width=72)*
>
> * Sort Key: health_timeseries_table.health*
>
> * ->  WindowAgg  (cost=0.98..497406.71 rows=50 width=72)*
>
> *   ->  Merge Left Join  (cost=0.98..491156.71 rows=50
> width=64)*
>
> * Merge Cond: (object_table.uuid =
> health_timeseries_table.mobid)*
>
> * ->  Unique  (cost=0.42..57977.00 rows=50
> width=64)*
>
> *   ->  Index Scan Backward using
> object_table_pkey on object_table  (cost=0.42..56727.00 rows=50
> width=64)*
>
> * Index Cond: (("timestamp" >= 0) AND
> ("timestamp" <= '1505990086834'::bigint))*
>
> * Filter: (tenantid = 'perspica'::text)*
>
> * ->  Materialize  (cost=0.56..426235.64 rows=55526
> width=16)*
>
> *   ->  Unique  (cost=0.56..425541.56 rows=55526
> width=24)*
>
> * ->  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))*
>
> *LOG:  duration: 1971.697 ms*
>
>
>
>
>
> 2.)
>
>
> Limit  (cost=457629.21..457629.26 rows=20 width=72)
>
>   ->  Sort  (cost=457629.21..458879.21 rows=50 width=72)
>
> Sort Key: health_timeseries_table.health
>
> ->  WindowAgg  (cost=367431.49..444324.39 rows=50 width=72)
>
>   ->  Merge Left Join  (cost=367431.49..438074.39 rows=50
> width=64)
>
> Merge Cond: (object_table.uuid =
> health_timeseries_table.mobid)
>
> ->  Unique  (cost=0.42..57977.00 rows=50 width=64)
>
>   ->  Index Scan Backward using object_table_pkey
> on object_table  (cost=0.42..56727.00 rows=50 width=64)
>
> Index Cond: (("timestamp" >= '0'::bigint)
> AND ("timestamp" <= '150599040'::bigint))
>
> Filter: (tenantid = 'perspica'::text)
>
> ->  Materialize  (cost=367431.07..373153.32 rows=55526
> width=16)
>
>   ->  Unique  (cost=367431.07..372459.24
> rows=55526 width=24)
>
> ->  Sort  (cost=367431.07..369945.16
> rows=1005634 width=24)
>
>   Sort Key:
> health_timeseries_table.mobid DESC, health_timeseries_table."timestamp"
> DESC, health_timeseries_table.health
>
>   ->  Seq Scan on
> health_timeseries_table  (cost=0.00..267171.00 rows=1005634 width=24)
>
>
> Filter: (("timestamp" >=
> '150598950'::bigint) AND ("timestamp" <= '150599040'::bigint))
>
> On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe <
> philippe.p...@worldline.com> wrote:
>
>> 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@postgresql.org
>> Objet : Re: [PERFORM] Slow query in JDBC
>>
>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
>> subramaniam31...@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 qu

Re: [PERFORM] Slow query in JDBC

[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.
Please let us know how this issue can be resolved from JDBC?

1.)


*Limit  (cost=510711.53..510711.58 rows=20 width=72)*

*   ->  Sort  (cost=510711.53..511961.53 rows=50 width=72)*

* Sort Key: health_timeseries_table.health*

* ->  WindowAgg  (cost=0.98..497406.71 rows=50 width=72)*

*   ->  Merge Left Join  (cost=0.98..491156.71 rows=50
width=64)*

* Merge Cond: (object_table.uuid =
health_timeseries_table.mobid)*

* ->  Unique  (cost=0.42..57977.00 rows=50
width=64)*

*   ->  Index Scan Backward using object_table_pkey
on object_table  (cost=0.42..56727.00 rows=50 width=64)*

* Index Cond: (("timestamp" >= 0) AND
("timestamp" <= '1505990086834'::bigint))*

* Filter: (tenantid = 'perspica'::text)*

* ->  Materialize  (cost=0.56..426235.64 rows=55526
width=16)*

*   ->  Unique  (cost=0.56..425541.56 rows=55526
width=24)*

* ->  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))*

*LOG:  duration: 1971.697 ms*





2.)


Limit  (cost=457629.21..457629.26 rows=20 width=72)

  ->  Sort  (cost=457629.21..458879.21 rows=50 width=72)

Sort Key: health_timeseries_table.health

->  WindowAgg  (cost=367431.49..444324.39 rows=50 width=72)

  ->  Merge Left Join  (cost=367431.49..438074.39 rows=50
width=64)

Merge Cond: (object_table.uuid =
health_timeseries_table.mobid)

->  Unique  (cost=0.42..57977.00 rows=50 width=64)

  ->  Index Scan Backward using object_table_pkey
on object_table  (cost=0.42..56727.00 rows=50 width=64)

Index Cond: (("timestamp" >= '0'::bigint)
AND ("timestamp" <= '150599040'::bigint))

Filter: (tenantid = 'perspica'::text)

->  Materialize  (cost=367431.07..373153.32 rows=55526
width=16)

  ->  Unique  (cost=367431.07..372459.24 rows=55526
width=24)

->  Sort  (cost=367431.07..369945.16
rows=1005634 width=24)

  Sort Key:
health_timeseries_table.mobid DESC, health_timeseries_table."timestamp"
DESC, health_timeseries_table.health

  ->  Seq Scan on
health_timeseries_table  (cost=0.00..267171.00 rows=1005634 width=24)


Filter: (("timestamp" >=
'150598950'::bigint) AND ("timestamp" <= '150599040'::bigint))

On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe <philippe.p...@worldline.com>
wrote:

> 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@postgresql.org
> Objet : Re: [PERFORM] Slow query in JDBC
>
> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
> subramaniam31...@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/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èces jointes sont confidentiels et réservés à l'usage
> exclusif de ses destinataires. Il peut également être protégé par le secret
> professionnel. Si vous recevez ce message par erreur, merci d'en avertir
> immédiatement l'expéditeur et de le détruire. L'intégrité du message ne
> pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra
> êtr

Re: [PERFORM] Slow query in JDBC

[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@postgresql.org
Objet : Re: [PERFORM] Slow query in JDBC

On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <subramaniam31...@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/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èces jointes sont confidentiels et réservés à l'usage 
exclusif de ses destinataires. Il peut également être protégé par le secret 
professionnel. Si vous recevez ce message par erreur, merci d'en avertir 
immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant 
être assurée sur Internet, la responsabilité de Worldline ne pourra être 
recherchée quant au contenu de ce message. Bien que les meilleurs efforts 
soient faits pour maintenir cette transmission exempte de tout virus, 
l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne 
saurait être recherchée pour tout dommage résultant 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 endeavours 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.!!!"

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

Re: [PERFORM] Slow query in JDBC

[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.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

Re: [PERFORM] Slow query in JDBC

[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
> > 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?
>
>
> I think jdbc always uses cursor, which can be problematic with default
> configuration, because postgres will try to generate plans that
> returns fast the first rows but not all the rows .  Can you try to
> configure cursor_tuple_fraction to 1 and see if that fixes your issue?
>

Re: [PERFORM] Slow query in JDBC

[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 please let us know the reason and how to fix this issue?


I think jdbc always uses cursor, which can be problematic with default
configuration, because postgres will try to generate plans that
returns fast the first rows but not all the rows .  Can you try to
configure cursor_tuple_fraction to 1 and see if that fixes your issue?


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

[PERFORM] Slow query in JDBC

[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 Regards
Subramaniam