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=500000 width=72)*
>
> * Sort Key: health_timeseries_table.health*
>
> * -> WindowAgg (cost=0.98..497406.71 rows=500000 width=72)*
>
> * -> Merge Left Join (cost=0.98..491156.71 rows=500000
> width=64)*
>
> * Merge Cond: (object_table.uuid =
> health_timeseries_table.mobid)*
>
> * -> Unique (cost=0.42..57977.00 rows=500000
> width=64)*
>
> * -> Index Scan Backward using
> object_table_pkey on object_table (cost=0.42..56727.00 rows=500000
> 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=500000 width=72)
>
> Sort Key: health_timeseries_table.health
>
> -> WindowAgg (cost=367431.49..444324.39 rows=500000 width=72)
>
> -> Merge Left Join (cost=367431.49..438074.39 rows=500000
> width=64)
>
> Merge Cond: (object_table.uuid =
> health_timeseries_table.mobid)
>
> -> Unique (cost=0.42..57977.00 rows=500000 width=64)
>
> -> Index Scan Backward using object_table_pkey
> on object_table (cost=0.42..56727.00 rows=500000 width=64)
>
> Index Cond: (("timestamp" >= '0'::bigint)
> AND ("timestamp" <= '1505990400000'::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" >=
> '1505989500000'::bigint) AND ("timestamp" <= '1505990400000'::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.!!!"
>>
>
>
