Re: Slow query in JDBC

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_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_table.objectname,object_table.datasourcename,object_table.datasourcetype,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.!!!"
>>>
>>
>>
>