Re: Store/Retrieve time series data from PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux