public inbox for [email protected]help / color / mirror / Atom feed
Slow query in JDBC 13+ messages / 5 participants [nested] [flat]
* Slow query in JDBC @ 2017-09-28 08:19 Subramaniam C <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Subramaniam C @ 2017-09-28 08:19 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 08:48 Julien Rouhaud <[email protected]> parent: Subramaniam C <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Julien Rouhaud @ 2017-09-28 08:48 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: pgsql-performance On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 08:58 Subramaniam C <[email protected]> parent: Julien Rouhaud <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Subramaniam C @ 2017-09-28 08:58 UTC (permalink / raw) To: Julien Rouhaud <[email protected]>; +Cc: pgsql-performance 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 <[email protected]> wrote: > On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C > <[email protected]> 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? > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 09:20 Julien Rouhaud <[email protected]> parent: Subramaniam C <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Julien Rouhaud @ 2017-09-28 09:20 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: pgsql-performance On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 09:26 Pavy Philippe <[email protected]> parent: Julien Rouhaud <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Pavy Philippe @ 2017-09-28 09:26 UTC (permalink / raw) To: Julien Rouhaud <[email protected]>; Subramaniam C <[email protected]>; +Cc: pgsql-performance https://www.postgresql.org/docs/current/static/auto-explain.html -----Message d'origine----- De : [email protected] [mailto:[email protected]] De la part de Julien Rouhaud Envoyé : jeudi 28 septembre 2017 11:21 À : Subramaniam C Cc : [email protected] Objet : Re: [PERFORM] Slow query in JDBC On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C <[email protected]> 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 ([email protected]) 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 09:59 Subramaniam C <[email protected]> parent: Pavy Philippe <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Subramaniam C @ 2017-09-28 09:59 UTC (permalink / raw) To: Pavy Philippe <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; pgsql-performance 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 <[email protected]> wrote: > https://www.postgresql.org/docs/current/static/auto-explain.html > > > -----Message d'origine----- > De : [email protected] [mailto:pgsql-performance- > [email protected]] De la part de Julien Rouhaud > Envoyé : jeudi 28 septembre 2017 11:21 > À : Subramaniam C > Cc : [email protected] > Objet : Re: [PERFORM] Slow query in JDBC > > On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < > [email protected]> 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 ([email protected]) > 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.!!!" > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 13:59 Dave Cramer <[email protected]> parent: Subramaniam C <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Dave Cramer @ 2017-09-28 13:59 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance 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 [email protected] www.postgresintl.com On 28 September 2017 at 05:59, Subramaniam C <[email protected]> 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 < > [email protected]> wrote: > >> https://www.postgresql.org/docs/current/static/auto-explain.html >> >> >> -----Message d'origine----- >> De : [email protected] [mailto: >> [email protected]] De la part de Julien Rouhaud >> Envoyé : jeudi 28 septembre 2017 11:21 >> À : Subramaniam C >> Cc : [email protected] >> Objet : Re: [PERFORM] Slow query in JDBC >> >> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < >> [email protected]> 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 ([email protected] >> ) >> 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.!!!" >> > > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 16:32 Subramaniam C <[email protected]> parent: Dave Cramer <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Subramaniam C @ 2017-09-28 16:32 UTC (permalink / raw) To: Dave Cramer <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance 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 <[email protected]> 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 > > [email protected] > www.postgresintl.com > > On 28 September 2017 at 05:59, Subramaniam C <[email protected]> > 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 < >> [email protected]> wrote: >> >>> https://www.postgresql.org/docs/current/static/auto-explain.html >>> >>> >>> -----Message d'origine----- >>> De : [email protected] [mailto: >>> [email protected]] De la part de Julien Rouhaud >>> Envoyé : jeudi 28 septembre 2017 11:21 >>> À : Subramaniam C >>> Cc : [email protected] >>> Objet : Re: [PERFORM] Slow query in JDBC >>> >>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < >>> [email protected]> 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.!!!" >>> >> >> > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-28 19:04 Dave Cramer <[email protected]> parent: Subramaniam C <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Dave Cramer @ 2017-09-28 19:04 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance 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 [email protected] www.postgresintl.com On 28 September 2017 at 12:32, Subramaniam C <[email protected]> wrote: > 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 <[email protected]> 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 >> >> [email protected] >> www.postgresintl.com >> >> On 28 September 2017 at 05:59, Subramaniam C <[email protected]> >> 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 < >>> [email protected]> wrote: >>> >>>> https://www.postgresql.org/docs/current/static/auto-explain.html >>>> >>>> >>>> -----Message d'origine----- >>>> De : [email protected] [mailto: >>>> [email protected]] De la part de Julien Rouhaud >>>> Envoyé : jeudi 28 septembre 2017 11:21 >>>> À : Subramaniam C >>>> Cc : [email protected] >>>> Objet : Re: [PERFORM] Slow query in JDBC >>>> >>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < >>>> [email protected]> 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.!!!" >>>> >>> >>> >> > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-29 05:57 Subramaniam C <[email protected]> parent: Dave Cramer <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Subramaniam C @ 2017-09-29 05:57 UTC (permalink / raw) To: Dave Cramer <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance 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 <[email protected]> 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 > > [email protected] > www.postgresintl.com > > On 28 September 2017 at 12:32, Subramaniam C <[email protected]> > 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 <[email protected]> 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 >>> >>> [email protected] >>> www.postgresintl.com >>> >>> On 28 September 2017 at 05:59, Subramaniam C <[email protected] >>> > 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 < >>>> [email protected]> wrote: >>>> >>>>> https://www.postgresql.org/docs/current/static/auto-explain.html >>>>> >>>>> >>>>> -----Message d'origine----- >>>>> De : [email protected] [mailto: >>>>> [email protected]] De la part de Julien Rouhaud >>>>> Envoyé : jeudi 28 septembre 2017 11:21 >>>>> À : Subramaniam C >>>>> Cc : [email protected] >>>>> Objet : Re: [PERFORM] Slow query in JDBC >>>>> >>>>> On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C < >>>>> [email protected]> 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.!!!" >>>>> >>>> >>>> >>> >> > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-29 06:49 Jeff Janes <[email protected]> parent: Subramaniam C <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Jeff Janes @ 2017-09-29 06:49 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C <[email protected]> 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" >= > '1505989500000'::bigint) AND ("timestamp" <= '1505990400000'::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 ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-29 10:44 Subramaniam C <[email protected]> parent: Jeff Janes <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Subramaniam C @ 2017-09-29 10:44 UTC (permalink / raw) To: Jeff Janes <[email protected]>; +Cc: Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance 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 <[email protected]> wrote: > On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C <[email protected] > > 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" >= >> '1505989500000'::bigint) AND ("timestamp" <= '1505990400000'::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 > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query in JDBC @ 2017-09-29 13:04 Dave Cramer <[email protected]> parent: Subramaniam C <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Dave Cramer @ 2017-09-29 13:04 UTC (permalink / raw) To: Subramaniam C <[email protected]>; +Cc: Jeff Janes <[email protected]>; Pavy Philippe <[email protected]>; Julien Rouhaud <[email protected]>; pgsql-performance Good catch Jeff. as for which version. We always recommend the latest version. 42.1.4 Dave Cramer [email protected] www.postgresintl.com On 29 September 2017 at 06:44, Subramaniam C <[email protected]> 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 <[email protected]> wrote: > >> On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C < >> [email protected]> 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" >= >>> '1505989500000'::bigint) AND ("timestamp" <= '1505990400000'::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 >> > > ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2017-09-29 13:04 UTC | newest] Thread overview: 13+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-09-28 08:19 Slow query in JDBC Subramaniam C <[email protected]> 2017-09-28 08:48 ` Julien Rouhaud <[email protected]> 2017-09-28 08:58 ` Subramaniam C <[email protected]> 2017-09-28 09:20 ` Julien Rouhaud <[email protected]> 2017-09-28 09:26 ` Pavy Philippe <[email protected]> 2017-09-28 09:59 ` Subramaniam C <[email protected]> 2017-09-28 13:59 ` Dave Cramer <[email protected]> 2017-09-28 16:32 ` Subramaniam C <[email protected]> 2017-09-28 19:04 ` Dave Cramer <[email protected]> 2017-09-29 05:57 ` Subramaniam C <[email protected]> 2017-09-29 06:49 ` Jeff Janes <[email protected]> 2017-09-29 10:44 ` Subramaniam C <[email protected]> 2017-09-29 13:04 ` Dave Cramer <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox