Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dsTOt-0002UN-2q for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 12:37:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dsTOs-0006IS-Mk for pgsql-performance@arkaria.postgresql.org; Thu, 14 Sep 2017 12:37:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dsTLH-0000I4-Ky for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 12:34:03 +0000 Received: from mail-lf0-x22b.google.com ([2a00:1450:4010:c07::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dsTLE-0006vv-EW for pgsql-performance@postgresql.org; Thu, 14 Sep 2017 12:34:02 +0000 Received: by mail-lf0-x22b.google.com with SMTP id u21so245006lfk.12 for ; Thu, 14 Sep 2017 05:34:00 -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=O1zfoM+F6urclD7YVTBynxCDVkJ3Qu8KlEHWN0R6xiw=; b=JU9IjYUJIn5HA52p/aM9Dbh21UdxDH42InH6VvfIYac723lWAoEYVLL/M39+BRkkGw bC4lG0IFwMz8rJh7kcdgO+n2w6WNCeKcCNaDw88O2YHCSQRh7hT+P67hJpupfPW5QmDo Cb6/Sr2bnQodVcT4ZVdw2sCAsWEbo8h+eRU5VsxljLm7KW+T4WuGRSKtGiDEootX+0CX rgwcRm/NIp4vYAkxNXv7SdZSe5XE6b8VPIDsjUeDAdh8sUNZLRJykWIivshPWSxe+ef2 8aUeSrBFVBwRrGCLxErMt+1161ETAT3lDbXYCkZUMr3GDYfON875ubZTMUaQ01+ewMj6 1XMQ== 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=O1zfoM+F6urclD7YVTBynxCDVkJ3Qu8KlEHWN0R6xiw=; b=PlUWlwuoG34LLdUwlNg7YNU9kkzFbptj3yCnZQS1dabF2TiDzWjTMie63q0rT7ltOq C/gJwrWhNO/rNUlWlfaKB/wRKOIv/EWoJwImfy6lJ7x4aBoyuadCaEIKytakpHIoV2I3 eAK6/NEKTBlhWbYmMSEaSbLz1z8VaGz4bioyeIEOepz4pfox6FUpAof+QbMifLtyOEat X6FTk5gbCME+YlolXh4D12/TgQx1qk58+ZORCUZFO9CUg5AnaZVSWKzDxy2TeDt60MeH Jel+7oePS5WCjkjHVxADSt8X140hWV4C2GbHYQkSei5TbtJyHZX1nzUNZbAGLf2ACTze +TZw== X-Gm-Message-State: AHPjjUi1HsfFXi2BUrly7s8NI01QSUpXuHOHULnAqpQGIHoo+ylTnxWk jQR0NlO89w1J1L6SaNqKlRu5h0/cRiL/64w+Q/0= X-Google-Smtp-Source: AOwi7QCRZp9I2Jh9t0ZQ1bUxfk3AZRV61CywZmPHDdRIwUAeuqXmGxAG8m2Ez7/+CGKbd3orVdops8jRwqbgCUyyQZU= X-Received: by 10.46.86.86 with SMTP id k83mr1513104ljb.28.1505392438743; Thu, 14 Sep 2017 05:33:58 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.235.85 with HTTP; Thu, 14 Sep 2017 05:33:58 -0700 (PDT) In-Reply-To: <1eb896cf2a6bdc3fd7e1567c86ecafe9@xs4all.nl> References: <1eb896cf2a6bdc3fd7e1567c86ecafe9@xs4all.nl> From: Subramaniam C Date: Thu, 14 Sep 2017 18:03:58 +0530 Message-ID: Subject: Re: Store/Retrieve time series data from PostgreSQL To: vinny Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a114f35828f15f50559257d58" 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 --001a114f35828f15f50559257d58 Content-Type: text/plain; charset="UTF-8" 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. > --001a114f35828f15f50559257d58 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I created index on morbid and hour together. Given below i= s the EXPLAIN output

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

=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:33 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.

--001a114f35828f15f50559257d58--