Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLSoh-003B6Q-IN for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 18:19:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qLSog-00DHNR-3H for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 18:19:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLSof-00DHJk-M6 for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 18:19:25 +0000 Received: from ikarus.unix-solution.de ([2a00:17d8:100:2::53f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLSoY-000wfT-05 for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 18:19:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=unix-solution.de; s=mx; h=Content-Transfer-Encoding:Content-Type: In-Reply-To:From:References:To:Subject:MIME-Version:Date:Message-ID:Cc: Content-Description:Content-ID:Resent-Cc:Resent-To; bh=0ryYj/mqYTqCr9PTh6dvv2LFugF+vcA1BT1ajO2/RKI=; b=fxSRuL9MIeabtXtHsfpNVJiZJx rnFCewrmL0tyavP+y0K1K0iMBb1Vj1ttdmWW362nfJz/vIgEEI+EsdpHjrvxdtejAz43hnWfapCMq eEu1OFjFhph/Gl6WyIWdRDsTjMyT01lg4rsRdZ/goFt/kq+qpo+Qq4xKpzDncqAQ4AGE=; Received: from dynamic-2a01-0c22-a57d-fe00-0226-c7ff-fe2c-2fd2.c22.pool.telefonica.de ([2a01:c22:a57d:fe00:226:c7ff:fe2c:2fd2]) by ikarus.unix-solution.de with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1qLSoR-00CLEV-1U for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 20:19:11 +0200 Message-ID: <4b927a7f-ff01-0df3-27b6-30b0fee17969@unix-solution.de> Date: Mon, 17 Jul 2023 20:19:08 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Query take a long time and use no index Content-Language: en-GB To: pgsql-general@lists.postgresql.org References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> <7599651d-7269-ec5b-b968-46795a76c375@enterprisedb.com> From: basti In-Reply-To: <7599651d-7269-ec5b-b968-46795a76c375@enterprisedb.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-ACL-Warn: Max 0 failed recipients allowed List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Thanks a lot tomas, i will try it. I have find out that there is a 'aggregation' function in the frontend. But this is MySQL specific and I have no idea the transform it to postgres. It looks like: 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) SELECT channel_id, ? AS type, MAX(agg.timestamp) AS timest amp, COALESCE( SUM(agg.val_by_time) / (MAX(agg.timestamp) - MIN(agg.prev_timestamp)), AVG(agg.value)) AS value, COUNT(agg.value) AS count FROM ( SELECT channel_id, timestamp, value, value * (timestamp - @prev_timestamp) AS val_by_time, COALESCE(@prev_timestamp, 0) AS prev_timestamp, @prev_timestamp := timestamp FROM data CROSS JOIN (SELECT @prev_timestamp := NULL) AS vars WHERE channel_id = ? AND timestamp < UNIX_TIMESTAMP(DATE_FORMAT(NOW(), "%Y-%m-%d")) * 1000 ) AS agg GROUP BY channel_ id, DATE_TRUNC('day', TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')' with params [3, 5]: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "REPLACE" LINE 1: REPLACE INTO aggregate (channel_id, type, timestamp, value, ... Am 17.07.23 um 13:21 schrieb Tomas Vondra: > On 7/17/23 13:20, Tomas Vondra wrote: >> ... >> >> It's always going to be slow with the COUNT(DISTINCT), I'm afraid. >> >> Not sure how much you can modify the query / database, and how accurate >> results you need. If you're OK with estimates, you can try postgres-hll >> extension [2] which estimates count(distinct). For exact reaults, I >> wrote count_distinct extension [2] that uses hashtable. Might be worth a >> try, I guess. >> > > Damn, I forgot to add the links: > > [1] https://github.com/citusdata/postgresql-hll > > [2] https://github.com/tvondra/count_distinct > > regards >