Re: Store/Retrieve time series data from PostgreSQL

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.
>