From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | Subramaniam C <subramaniam31784(at)gmail(dot)com> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Pavy Philippe <Philippe(dot)Pavy(at)worldline(dot)com>, Julien Rouhaud <rjuju123(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Slow query in JDBC |
Date: | 2017-09-29 13:04:46 |
Message-ID: | CADK3HH+68Swe-takYvrk++3T_gsUBeMUoVOntuM13HWuoyrOOQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Good catch Jeff.
as for which version. We always recommend the latest version. 42.1.4
Dave Cramer
davec(at)postgresintl(dot)com
www.postgresintl.com
On 29 September 2017 at 06:44, Subramaniam C <subramaniam31784(at)gmail(dot)com>
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 <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> On Thu, Sep 28, 2017 at 2:59 AM, 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.)
>>>
>>>
>>> * -> 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
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | milist ujang | 2017-09-29 23:07:06 | BDR, wal sender, high system cpu, mutex_lock_common |
Previous Message | Subramaniam C | 2017-09-29 10:44:03 | Re: Slow query in JDBC |