Re: Slow query in JDBC

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 <your query>
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 upgrade my JDBC driver? Will it help resolving
this issue?

Please help.

Thanks and Regards
Subramaniam

On Fri, Sep 29, 2017 at 12:34 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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 <your query>
>
> then explain execute foo;
>
> FWIW upgrading the driver won't help this situation but there's still no
> reason not to upgrade.
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
> On 28 September 2017 at 12:32, Subramaniam C <subramaniam31784(at)gmail(dot)com>
> wrote:
>
>> The JDBC version is 9.4-1201-jdbc41.
>>
>> Query :-
>>
>> select count(*) OVER() AS count,uuid,availability,object
>> name,datasourcename,datasourcetype,objecttype,health from (select
>> distinct on (health_timeseries_table.mobid) mobid,
>> health_timeseries_table.health, health_timeseries_table.timestamp from
>> health_timeseries_table where timestamp >= 1505989186834 and timestamp <=
>> 1505990086834 ORDER BY health_timeseries_table.mobid DESC,
>> health_timeseries_table.timestamp DESC, health_timeseries_table.health
>> ASC) t right join (SELECT DISTINCT ON (object_table.uuid) uuid,
>> object_table.timestamp,object_table.availability,object_tabl
>> e.objectname,object_table.datasourcename,object_table.dataso
>> urcetype,object_table.objecttype FROM object_table where
>> object_table.timestamp >= 0 and object_table.timestamp <= 1505990086834 and
>> object_table.tenantid = 'perspica' ORDER BY object_table.uuid DESC,
>> object_table.timestamp DESC)u on (t.mobid = u.uuid) order by health asc
>> limit 20 offset 0;
>>
>>
>> Please let us know any other details?
>>
>>
>> Thanks and Regards
>>
>> Subramaniam
>>
>> On Thu, Sep 28, 2017 at 7:29 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>
>>> 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
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>> On 28 September 2017 at 05:59, Subramaniam C <subramaniam31784(at)gmail(dot)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(dot)Pavy(at)worldline(dot)com> wrote:
>>>>
>>>>> https://www.postgresql.org/docs/current/static/auto-explain.html
>>>>>
>>>>>
>>>>> -----Message d'origine-----
>>>>> De : pgsql-performance-owner(at)postgresql(dot)org [mailto:
>>>>> pgsql-performance-owner(at)postgresql(dot)org] De la part de Julien Rouhaud
>>>>> Envoyé : jeudi 28 septembre 2017 11:21
>>>>> À : Subramaniam C
>>>>> Cc : pgsql-performance(at)postgresql(dot)org
>>>>> Objet : Re: [PERFORM] Slow query in JDBC
>>>>>
>>>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <
>>>>> subramaniam31784(at)gmail(dot)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(at)postgresql(dot)
>>>>> 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.!!!"
>>>>>
>>>>
>>>>
>>>
>>
>