public inbox for [email protected]  

help / color / mirror / Atom feed

From: Subramaniam C <[email protected]>
To: vinny <[email protected]>
Cc: [email protected]
Subject: Re: Store/Retrieve time series data from PostgreSQL
Date: Thu, 14 Sep 2017 18:03:58 +0530
Message-ID: <CAL=06WnWzjZ9_HF1HopGPjUNE2F-Gcy7NBRDP7Ms43_SFyj6JQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAL=06WmUGXv2Td30XaT9mHYSWJCVombq85+z2Th+0UPs7sOKsQ@mail.gmail.com>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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 <[email protected]> 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.
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Store/Retrieve time series data from PostgreSQL
  In-Reply-To: <CAL=06WnWzjZ9_HF1HopGPjUNE2F-Gcy7NBRDP7Ms43_SFyj6JQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox