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 1w6NlV-004FKu-1t for pgsql-general@arkaria.postgresql.org; Sat, 28 Mar 2026 07:07:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w6NlT-00DfeI-32 for pgsql-general@arkaria.postgresql.org; Sat, 28 Mar 2026 07:07:24 +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.96) (envelope-from ) id 1w6NlT-00DfeA-1C for pgsql-general@lists.postgresql.org; Sat, 28 Mar 2026 07:07:24 +0000 Received: from mail-oo1-xc29.google.com ([2607:f8b0:4864:20::c29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w6NlR-00000001SOT-3fP7 for pgsql-general@lists.postgresql.org; Sat, 28 Mar 2026 07:07:22 +0000 Received: by mail-oo1-xc29.google.com with SMTP id 006d021491bc7-67e09232daeso1712959eaf.2 for ; Sat, 28 Mar 2026 00:07:21 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1774681641; cv=none; d=google.com; s=arc-20240605; b=bFGLd5XLpZWnt4tpx0pj9VnmbtC/8ufufyUzD0K7KLoaxNh71FIBZst/na2VcMwc5E TX2U1jF9BCkmKdLQfKubUPZ2h9hIlWnz0Cx7bAI1/HyJVVmmT1M2etuHnClN8u6l7fZd /O2x7DHEc15HYIzJkVZkhWPCDru6YxLDOw22nH6O4qThyWZv3+TUuzDOlt3ZDiHO4mCS 6lAouHzCaBJzmgP70v9Z8Zir/kNESInn0PQrAz26zfwCgSX9oSth+xjSvgJ3+doit2O/ nWy+84xzj4xonYh3mlUNfHxc8wsdlOK2kL8WvPc8T7JnxISUD5dYYUQkUbcmOMmUIf4m 1j0w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=wJ6gBRvPQqzk6a5ZBIuF0Mzw4D5ZijCPXBHQ60j6zxE=; fh=4rCG0PM8n0FOokGy8sSWDJpgPdCgp6yIXcpABJ7tUh0=; b=FWZQU1YcHYghKS/ac5VwKb5rZ8/2u2kaBZtlCspD25cyNSiS+vxw4f406ofeDfcATr UhaKglKIBElVgvyWWyCEuC7/wOYPOQ21lWM49NDUa0kdcrm+m3JxpdzZ5NE4USDW+rA0 pfnXXUy3VV30/ACgVBeMOZzkvAQQm2HpFFnPNubSD6hcMDbUaAZ7W+iGMneRIN7ldA7a VBHD8vdNghaSY+f64epo6Jq8ACsO0keXoUQnwHWT0aRdFxMpn5eFMz2HsJ1F9UGRuWr9 L4BzgKJbUcPLZdS5Ffvpa5R109M51N/F2UPDdmA0pqpgGgXBCGY3KILBf1bRILPbYcoQ 8YAA==; darn=lists.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=1774681641; x=1775286441; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=wJ6gBRvPQqzk6a5ZBIuF0Mzw4D5ZijCPXBHQ60j6zxE=; b=SVTsg3t5kalaASYp+cwmITrD0C75zZOiSUlVG/V9ivkUNXUvcwDoOxflg5eJD+wYpb D0DnLkIyllWIFo4Qfqx1q5H34s+oXHt+ylrRRoMWD35vEqBK6bWZmMNzdwQtoOuY9ejT 8AYZts3SQAMiipJdjiqIDJjcxLLYv8QSzGx7hk8gqt47oyhiCCR708i64S+TgZjAKS+9 NANQdbBzQvnrB3XGrN/uGrCflxK3NwMPX/e3dGyPwo8etvympYHCfdaV0AyywCw5AWBA +TWMxJR5WENc3SxywnrU+hTYKGlGAmV8VbmG6PwFDKLodXKBvGIt1sQ469x0ziImKYhJ rYmQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1774681641; x=1775286441; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=wJ6gBRvPQqzk6a5ZBIuF0Mzw4D5ZijCPXBHQ60j6zxE=; b=S1B7ZSNWleEv4h8e0NV6iq/xVYD4FJbwaHy2c8DkbHlenagaVpOZz7ovxJh6nOTiIM iM8eOx0VEXzZnZ0iwwOYwANsyVeTiw9NWYAjLi1h4cfsjy3AFY4nyss1nsEtD7peRzdc 3QkXNdIdm+360EvzpbAB9rxwPr0KahDf0FiDMKLpDup6pbNa/3/MqHqcdbLttcyyp++G ubkiqC1YQHCVTruDnnleO8NtXW8W6LUB+sOE6+zw2ddtuvKWE+8KWbaP0fR8cAqWuJkd g1lK6UtGGCvBgLGg0q0qPHGHOZ3MF5BmTQZNCzwce7C81mFe9pc0tPHbRNq79vs/9MGJ HXFA== X-Gm-Message-State: AOJu0YxoA24DWgaNU80ggPFTKkQfA28g9C+uy1KzOdZw4ZvLDW+9eDQ0 8DXfYlWOOpSd5SwG3c1fYPw2vUtvNjOD/TmeoNuOJp/C1cqmtcnHfuEQ3+Pp6TIwzuGRNwmD74S 5NqBsGjrb26rLXC8gwUDFvgKwQgi+bQzZ00he X-Gm-Gg: ATEYQzyMR4Zvs2fPPUWJbAT5eCL77qqktjjZ/L7PcCGgSFtS5OTKsYJsQxX8+RfyvoO gxi292qO5Vh8mx+7tylUDmAPAq58IsZXhIki5XBhHTr98pBc10GQuwFZ0ADLyWXPSCkUO/tNRWm Z+gZLA/0u0+5vsIlz0wupiE3Ma/nBuRGIZnxRGMtGT5eLao/0/GjBx8muvFzGfTnQwxrn1DeRjK zVt9C73Zgy39euP33EvjjpHlBiGeuZ85/v+63plPSM3UZ+N0Ddgs2VBy0tf9bjptpxfA3gOUqFO VC2gbyujQQ== X-Received: by 2002:a05:6820:221a:b0:67c:2b6b:520c with SMTP id 006d021491bc7-67e1875a871mr2724509eaf.62.1774681641109; Sat, 28 Mar 2026 00:07:21 -0700 (PDT) MIME-Version: 1.0 From: Hua W Peng Date: Sat, 28 Mar 2026 15:07:09 +0800 X-Gm-Features: AQROBzDwDS8NzqF2V8d9541fz6AIwuQABxN41X-fZV-cLHXN_rc0zavh0qxoEYk Message-ID: Subject: slow sql query for big items To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ceccad064e1044ec" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ceccad064e1044ec Content-Type: text/plain; charset="UTF-8" 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. In this case, even a simple COUNT(*) query becomes extremely slow, taking about 7-8 minutes to finish. I am running PostgreSQL 14 on Ubuntu 22.04 with a 24GB shared buffer. 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. Can you help me? Thanks. --000000000000ceccad064e1044ec Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,

I have a common table for teleme= try 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 | Collation | Nullable | Defaul= t=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 |=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 | num= eric(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<= span class=3D"gmail-Apple-converted-space">=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<= span class=3D"gmail-Apple-converted-space">=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<= span class=3D"gmail-Apple-converted-space">=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()

Indexe= s:

=C2=A0 =C2=A0 "dms_data_g= zdy_pkey" PRIMARY KEY, btree (record_time, switch_oid)

=C2=A0 =C2=A0 "dms_data_g= zdy_record_time_idx" btree (record_time DESC)

=C2=A0 =C2=A0 "idx_dms_fe= eder_gis_id" btree (feeder_gis_id, record_time)

=C2=A0 =C2=A0 "idx_dms_st= ation_name" btree (station_name, record_time)

=C2=A0 =C2=A0 "idx_dms_sw= itch_oid" btree (switch_oid, record_time)


Dat= a records are growing by about 10 million every day, reaching 300= million per month. In this case, even a simple COUNT(*) q= uery becomes extremely slow, taking about 7-8 minutes to finish.

I am running PostgreSQL 14 on Ubuntu 22.04 with a 24GB shared buffer.

<= p class=3D"gmail-p1" style=3D"margin:0px;font-variant-numeric:normal;font-v= ariant-east-asian:normal;font-variant-alternates:normal;font-size-adjust:no= ne;font-kerning:auto;font-feature-settings:normal;font-stretch:normal;font-= size:11px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)">And, though in our test en= v we have timescaledb enabled:


Triggers:

=C2=A0 =C2=A0 ts_insert_blocker BEFORE I= NSERT ON dms_data_gzdy FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions= .insert_blocker()

Nu= mber of child tables: 9 (Use \d+ to list them.)


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

<= br>


Ca= n you help me?

=

Thanks.

--000000000000ceccad064e1044ec--