public inbox for [email protected]  

help / color / mirror / Atom feed

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

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.


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

* 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