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.96) (envelope-from ) id 1w6TcB-004L6y-1F for pgsql-general@arkaria.postgresql.org; Sat, 28 Mar 2026 13:22:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w6Tc9-00EDw9-26 for pgsql-general@arkaria.postgresql.org; Sat, 28 Mar 2026 13:22:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w6Tc9-00EDw0-0n for pgsql-general@lists.postgresql.org; Sat, 28 Mar 2026 13:22:09 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w6Tc7-00000001eu0-0bRK for pgsql-general@postgresql.org; Sat, 28 Mar 2026 13:22:09 +0000 Received: by mail-oi1-x22b.google.com with SMTP id 5614622812f47-46aa216a5edso273358b6e.0 for ; Sat, 28 Mar 2026 06:22:07 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1774704124; cv=none; d=google.com; s=arc-20240605; b=T5l49sQ/2+yKpZZYmJsWtiitFg8y8MdUapFbQs2C2qV8YzTYD53H7MHv8uc2AYVPCS QI8/RKMmuuterk4KMsP5uoAEwIOc7Xc/gNvT6JjIv0rMFd12H5rqOukocfdDH19zfiZV xlXAhtkxQWX+cZWk0opbcronXf4hk0jnEEzhRmajjlt0KSNrx+UGoCX+fQEFk9WsPmQr F4QV0Yd9pJKh39PDvdZZO+Rlf58MXm8HTSCtaFjnyLDZ1pcducyENBtVVrYO9RObzgEp E9kKG8aOgjU/FuKi9D97wv8gmhcR6puR3KYFDOkhJCL6gtNP5ihqnDvCXBZRL92fznyn NYhA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=nNu0NvjoWheEoqg8kZklS96EFWjyP1wjUaWNoQT6LlE=; fh=KNSq+t9BltSXFnT3Yof/aKGBtqxeA+bTALiYdvTslaY=; b=e7t5psm0BUxzFBIPuXhqUrBsSVM+Xa1euf7wkJw3M6qDFpaNTh3pJ9W5ZYMfqh164Y Oo3pBelTIdMui4vL/jD7TWxUz3pmm6prQw6+EB2+i1Mr92iWYvnHKWErTn8otLB9zVL4 PcMd1yJSAb1ywHGIqQGcuCJBojUN/IuWwSRmID1Z5FOlaHGPAS2BAsNmpD1wmPvxA5ZW K8WywfpVYD8ULMvSl17GBFdvAjXV/0QQ6HOayru2eRzK7gbuYlIdMFe54i2SK2YcEjcR Ax31YtHwrp8BwnU/+f/pV01S+BdM50K9XRYNE7nRoUXCCOzokLAF89pLg6c3il78VJUS Ob4w==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1774704124; x=1775308924; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=nNu0NvjoWheEoqg8kZklS96EFWjyP1wjUaWNoQT6LlE=; b=M6vJ4XMMpSqATvro8zvM6NCUPpQrOuSK65eLHnBW8revx14B7P2Y514Jhem8ZqCkLz C9DE8irjoM2/hT9/TI+uc2dg04n6DFH1Fk32SA4ddGeUmbMaprJ4H+zPL3AzrLIjk+29 vHJRMFcWMJwqk0iCsr8CTBqpbk83CeTCjy4y3tpQtzBxPI/9s4D1jTDl2VycbRrGWEb7 mmj3BfG1619imCEwamhnDK71Kwrm72SUgabkP4OWQt4CATkJfw6vq7iq+5kYtAYcvJNY 7tn7grgQUkw1T67oF5DDI0KS2C3gmBEQkFYL2ZZa6yghc/CKAC+bHjh3eu405zlvduKa yrHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1774704124; x=1775308924; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=nNu0NvjoWheEoqg8kZklS96EFWjyP1wjUaWNoQT6LlE=; b=HQntq9DNTRCmD5PIjqqN+hCe2P5u1JlV0SHLOC6zi8onS58AK3bl/wZzBez+Wjdegx EVFTnASIGBEER5ZrgCKwwiA6pW3MllLSjbDaRMJ1HrIj1ryk0xfjEH3o0yEi9vFpkiOH 1DYn9FfDjKl8Ey2IbdgYoaDmP30q0OeGm/nKRyxhFjt9ATC3ddegZEch1P7heOxaEMLH ZQrFfw1IejV2NYWZDmlrJHT5+sq6CxD4oXc4yZnCgDwhIdJ/gRpYe/rjI4iGWtBtgAbd NS+Jf9mvWQ1wEAYiI8Ges+H+EvGn4KfnV5WPoTQOaMZoVcTOXDPvSTdem+MXUXCPWjti NecQ== X-Gm-Message-State: AOJu0Yy7xdvLckEXehxXlcRZCD3Vqc0rwI4gdnjDZgrIbS8wq+D0nO4b DVqtWWZHYOlWAkbSslpEe66j2jk4J5ntOlc1LKK1se6QgqrpLdYaetpuhGD3XMmtIZAVkQkmWRV ZZ4CysffK+2A+7OBRzbv78J1A96SfHeQJ9w== X-Gm-Gg: ATEYQzxD4I/O+ZR3Q+hjIGG5SStLquiZzeCflo3MRSZg/HJVgllL5neT5UzHjFF9yhu 8b9dMlbvNcoeKW6yOy62FXXLmH3ccJ+cryxx8P8+hXbw7nuAwAYcm0SZzIaFd8SK62nqKCFH34/ unvEinGuezrqVneIPUEnk0ghe+FwmzPbfDy3s/kjeJ0MpD3WuWLnkCF55KZOyzjxBCUOYTAOuUJ YfbQQapI8ewp8EKX4A9fvdrCpXrBijwdM3jOORVzSSyIljAGumY+8rTBRlQHcO96Eyw2dCGZ+Cs 3INCA+RI X-Received: by 2002:a05:6808:1a0e:b0:467:15ad:9df0 with SMTP id 5614622812f47-46a8a3bbce8mr3097702b6e.7.1774704124446; Sat, 28 Mar 2026 06:22:04 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Sat, 28 Mar 2026 09:21:53 -0400 X-Gm-Features: AQROBzCuPrKYc95771_t6_g8BwGZ0Dlo7yc6kOcYMLEqEdLxQqFfDjgnikZ2mTg Message-ID: Subject: Re: slow sql query for big items To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000eb4bd8064e1580cf" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eb4bd8064e1580cf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Mar 28, 2026 at 3:07=E2=80=AFAM Hua W Peng wro= te: > Hello, > > I have a common table for telemetry data. the stru is: > > Column | Type | Collation | Nullable > | Default > > > ------------------------+--------------------------+-----------+---------= -+--------- > > record_time | timestamp with time zone | | not null > | > > station_name | text | | > | > > feeder_gis_id | text | | > | > > switch_name | text | | > | > > switch_oid | text | | not null > | > > switch_gis_id | text | | > | > > switch_status | integer | | > | > > switch_status_quality | integer | | > | > > active_power | numeric(18,6) | | > | > > active_power_quality | integer | | > | > > reactive_power | numeric(18,6) | | > | > > reactive_power_quality | integer | | > | > > current_a | numeric(18,6) | | > | > > current_a_quality | integer | | > | > > current_b | numeric(18,6) | | > | > > current_b_quality | integer | | > | > > current_c | numeric(18,6) | | > | > > current_c_quality | integer | | > | > > voltage_uab | numeric(18,6) | | > | > > voltage_uab_quality | integer | | > | > > voltage_ubc | numeric(18,6) | | > | > > voltage_ubc_quality | integer | | > | > > voltage_uca | numeric(18,6) | | > | > > voltage_uca_quality | integer | | > | > > created_at | timestamp with time zone | | = | > now() > > Indexes: > > "dms_data_gzdy_pkey" PRIMARY KEY, btree (record_time, switch_oid) > > "dms_data_gzdy_record_time_idx" btree (record_time DESC) > > "idx_dms_feeder_gis_id" btree (feeder_gis_id, record_time) > > "idx_dms_station_name" btree (station_name, record_time) > > "idx_dms_switch_oid" btree (switch_oid, record_time) > > > Data records are growing by about *10 million* every day, reaching *300 > million* per month. > How many months of data? Is the production table partitioned? If so, by what date range? > In this case, even a simple COUNT(*) query becomes extremely slow, taking > about 7-8 minutes to finish. > > I am running PostgreSQL 14 > What minor version? > on Ubuntu 22.04 with a 24GB shared buffer. > Is that 25% of total RAM? What's the effective_cache_size? And, though in our test env we have timescaledb enabled: > > > Triggers: > > ts_insert_blocker BEFORE INSERT ON dms_data_gzdy FOR EACH ROW EXECUTE > FUNCTION _timescaledb_functions.insert_blocker() > > Number of child tables: 9 (Use \d+ to list them.) > > > But in production env there is no timescaledb which can't be installed as > well. > Laurenz is right: installing and using timescale in your *test* system *tes= ts timescale*. Why are you testing timescale when you can't install it in prod= ? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000eb4bd8064e1580cf Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sat, Mar 28, 2026 at 3:07=E2=80=AFAM H= ua W Peng <huawaltp@gmail.com&= gt; wrote:
Hello,

I have a common table for telemetry data. the stru is:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= /span>| Collation | Nullable | Default=C2=A0

------------------------+-------------------= -------+-----------+----------+---------

=C2=A0record_time=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | timestamp with time zone | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |=C2=A0=

=C2=A0station_name =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0feeder_gis_id=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0switch_name=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= /span>|=C2=A0

=C2=A0switch_oid =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | not null |=C2=A0

=C2=A0switch_gis_id=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0switch_status=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0switch_status_quality=C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0active_power =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0active_power_quality =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0reactive_power =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0reactive_power_quality | = integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0current_a=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0

=C2=A0current_a_quality= =C2=A0 =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0current_b=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0

=C2=A0current_b_quality= =C2=A0 =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0current_c=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0

=C2=A0current_c_quality= =C2=A0 =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0voltage_uab=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0voltage_uab_quality= =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0voltage_ubc=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0voltage_ubc_quality= =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0voltage_uca=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | numeric(18,6)=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0

=C2=A0voltage_uca_quality= =C2=A0 =C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0

=C2=A0created_at =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | timestamp with time zone | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | now()

Indexes:

=C2=A0 =C2=A0 "dms_data_gz= dy_pkey" PRIMARY KEY, btree (record_time, switch_oid)

=C2=A0 =C2=A0 "dms_data_gz= dy_record_time_idx" btree (record_time DESC)

=C2=A0 =C2=A0 "idx_dms_fee= der_gis_id" btree (feeder_gis_id, record_time)

=C2=A0 =C2=A0 "idx_dms_sta= tion_name" btree (station_name, record_time)

=C2=A0 =C2=A0 "idx_dms_swi= tch_oid" btree (switch_oid, record_time)


Data= records are growing by about 10 million every day, reaching 300 = million per month.

How many months of = data?=C2=A0

Is the production table partitioned?= =C2=A0 If so, by what date range?

In this case, even a simple C= OUNT(*) query becomes extremely slow, taking about 7-8 minutes to fi= nish.

I am running PostgreS= QL 14

What minor version?=C2=A0

on U= buntu 22.04 with a 24GB shared buffer.

Is = that 25% of total RAM?

What's the effective_ca= che_size?=C2=A0

And, though= in our test env we have timescaledb enabled:


Trigger= s:

=C2=A0 =C2=A0 <= /span>ts_insert_blocker BEFORE INSERT ON dms_data_gzdy FOR EACH ROW EXECUTE= FUNCTION _timescaledb_functions.insert_blocker()

Number of child tables: 9 (Use \d+ to li= st them.)


But in production env there is no timescaledb which = can't be installed as well.


Laurenz is right: installing and=C2=A0using ti= mescale in your test=C2=A0system tests timescale. Why are you= testing timescale when you can't install it in prod?

--
Death to <Redacted>, and but= ter sauce.
Don't boil me, I'm still alive.
<Red= acted> lobster!
--000000000000eb4bd8064e1580cf--