Re: Store/Retrieve time series data from PostgreSQL

With this query I am trying to get the latest hour for a given timestamp so
that I can get whole health array of all object for a given hour. So I am
doing DISTINCT on mobid and order by hour and mobid DESC.

On Thu, Sep 14, 2017 at 6:03 PM, Subramaniam C <subramaniam31784(at)gmail(dot)com>
wrote:

> I created index on morbid and hour together. Given below is the EXPLAIN
> output
>
> ------------------------------------------------------------
> ------------------------------
>
> Unique (cost=606127.16..621098.42 rows=1087028 width=200)
>
> -> Sort (cost=606127.16..613612.79 rows=2994252 width=200)
>
> Sort Key: mobid DESC, hour DESC
>
> -> Seq Scan on health_timeseries (cost=0.00..284039.00
> rows=2994252 width=200)
>
> Filter: ((hour >= '418134'::bigint) AND (hour <=
> '418135'::bigint))
>
> On Thu, Sep 14, 2017 at 5:33 PM, vinny <vinny(at)xs4all(dot)nl> wrote:
>
>> On 2017-09-14 13:51, Subramaniam C wrote:
>>
>>> Hi
>>>
>>> QUERY :-
>>>
>>> _select distinct on (health_timeseries.mobid) mobid,
>>> health_timeseries.health, health_timeseries.hour from
>>> health_timeseries where hour >=(1505211054000/(3600*1000))-1 and hour
>>> <= 1505211054000/(3600*1000) ORDER BY health_timeseries.mobid DESC,
>>> health_timeseries.hour DESC;_
>>>
>>>
>> Did you run EXPLAIN on this query to see what it is actually doing?
>>
>> What you are doing how is selecting all rows from the last hour,
>> sorting them by mobid and hour, and then DISTINCT filters out al
>> duplicates.
>>
>> Sorting on mobid is therefor useless, DISTINCT still has to check all
>> rows.
>>
>> Sorting on mobid and hour will take a long time if there is no index for
>> it,
>> so if you don't have an index on the mobid and hour together then you
>> should probably try that.
>>
>>
>> But, see what EXPLAIN tells you first.
>>
>> Regards,
>> Vincent.
>>
>
>