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 <vi...@xs4all.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. >