Re: Store/Retrieve time series data from PostgreSQL

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.