Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dsTPd-0002W4-Fw for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 12:38:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dsTPd-0006RS-3e for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 12:38:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dsTPb-0006MV-EJ for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 12:38:31 +0000 Received: from mail-lf0-x22b.google.com ([2a00:1450:4010:c07::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dsTPY-0007FV-Sn for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 12:38:30 +0000 Received: by mail-lf0-x22b.google.com with SMTP id c80so7630132lfh.0 for ; Thu, 14 Sep 2017 05:38:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=8hZCCw29DVfXgFaXx9YWIwqursgSGc4rdJ79obnCLPs=; b=HSn2B2baw73CxZ7NSha8JwEFquYjssgQ8ZwiwlOeYBgHsc3w4BTxn6uuDjEOJgRU15 YsILzmlaWl+YhdQxte+yP1CB30Xk/hhex+yTWNGOup6YutDC6L5Ms8mBNwfNwnWUku5c W3//o/h7Q65s72InF/JzlvfjuWUItnfZRGzwjWBtp2DzToECqFi0xrp35zZEC4pXnt2E Bg2W+m8vMocAEtSihlYQRPMwm981FbeGjnHnrIlVwXsYbS43b4BqS5oP/o+xGcwtNzfE unoJ5ACihAY0oVJjJrBlCiBjfHwy8Gixxd62xT1Ou8TNx0xVs8ukSDbRCBXrhbPYSyIZ ulfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=8hZCCw29DVfXgFaXx9YWIwqursgSGc4rdJ79obnCLPs=; b=K25j5zhn2ngZYnwAirXX2wIqGFJ7uoNqoutR4hSbC8plbwVC8FkUN83C4AWIiah21O D11APXKpW68WxQxXcus7BHBnXLhSNT8ttNW3CTXWTmuTvXGF6pTEeBOsCe8yeCZr4VqU C70qigDl8/yD6069SuPFtUDI6JdyBIpQ7zA7UziLVXFwPgjCnu+u6VGZjQ24nRZeOIPt jri4WKko3vBpI1Tq2wnkiiItkmqblPRcLKMpxC0H4ujbtVAeWo+bOcrBzGl6kqKWL0NT VIVvl7csDleR/Ojs+FywontapOJ0xAYWL84kUzDB2HEiycWQysn6wmQ+uweMycSM0kPg Auew== X-Gm-Message-State: AHPjjUjTbt9ydzMC2BGckK9aa3xzjqnd1xmCmfBci1BVt+EbzqWOFPTj g5+ZqfxHAMJ8RtaHqSmuveHn2bfbWmsckHznPg4= X-Google-Smtp-Source: AOwi7QBGsdYBGLIVdt8oeWX9QOdm5V1qFu0oX4H9zT3uA6+fjavIrS3MgOP+GOH6f6fmz3ZJLR6aoXgRVfUjCNfyn1g= X-Received: by 10.46.84.2 with SMTP id i2mr8685813ljb.60.1505392708062; Thu, 14 Sep 2017 05:38:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.235.85 with HTTP; Thu, 14 Sep 2017 05:38:27 -0700 (PDT) In-Reply-To: References: <1eb896cf2a6bdc3fd7e1567c86ecafe9@xs4all.nl> From: Subramaniam C Date: Thu, 14 Sep 2017 18:08:27 +0530 Message-ID: Subject: Re: Store/Retrieve time series data from PostgreSQL To: vinny Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="f403045fb9949c91940559258d74" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --f403045fb9949c91940559258d74 Content-Type: text/plain; charset="UTF-8" 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 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 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. >> > > --f403045fb9949c91940559258d74 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
With this query I am trying to get the latest hour for a g= iven timestamp so that I can get whole health array of all object for a giv= en hour. So I am doing DISTINCT on mobid and order by hour and mobid DESC.<= /div>

On Thu, Sep = 14, 2017 at 6:03 PM, Subramaniam C <subramaniam31784@gmail.com> wrote:
I cr= eated index on morbid and hour together. Given below is the EXPLAIN output<= div>

-------------------------------------------------= -----------------------------------------

=C2=A0Unique=C2=A0 (cost=3D606127.16..621098.42 rows=3D1087028 width=3D20= 0)

=C2=A0=C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D606127.16..613612.79 rows=3D2= 994252 width=3D200)

=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: mobid DESC, hour DESC<= /p>

=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on health_timeseri= es=C2=A0 (cost=3D0.00..284039.00 rows=3D2994252 width=3D200)

=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((hour >= ;=3D '418134'::bigint) AND (hour <=3D '418135'::bigint))=


On Thu, Sep 14, 2017 at 5:3= 3 PM, vinny <vinny@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 >=3D(1505211054000/(3600*1000))-1 and = hour
<=3D 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 s= hould probably try that.


But, see what EXPLAIN tells you first.

Regards,
Vincent.


--f403045fb9949c91940559258d74--