public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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:08:27 +0530
Message-ID: <CAL=06Wm7nhqx84j+3uFaaCeN6sAuvMmZUmOZVR3oKpYjNN+_QQ@mail.gmail.com> (raw)
In-Reply-To: <CAL=06WnWzjZ9_HF1HopGPjUNE2F-Gcy7NBRDP7Ms43_SFyj6JQ@mail.gmail.com>
References: <CAL=06WmUGXv2Td30XaT9mHYSWJCVombq85+z2Th+0UPs7sOKsQ@mail.gmail.com>
<[email protected]>
<CAL=06WnWzjZ9_HF1HopGPjUNE2F-Gcy7NBRDP7Ms43_SFyj6JQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
With this query I am trying to get the latest hour for a given timestamp so
that I can get whole health array of all object for a given hour. So I am
doing DISTINCT on mobid and order by hour and mobid DESC.
On Thu, Sep 14, 2017 at 6:03 PM, Subramaniam C <[email protected]>
wrote:
> 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=06Wm7nhqx84j+3uFaaCeN6sAuvMmZUmOZVR3oKpYjNN+_QQ@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