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 1t6AYt-00B7Kk-3K for pgsql-general@arkaria.postgresql.org; Wed, 30 Oct 2024 15:24:43 +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 1t6AYr-009qet-05 for pgsql-general@arkaria.postgresql.org; Wed, 30 Oct 2024 15:24:41 +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 1t6AYq-009qdI-F3 for pgsql-general@lists.postgresql.org; Wed, 30 Oct 2024 15:24:41 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t6AYj-003dlR-AR for pgsql-general@postgresql.org; Wed, 30 Oct 2024 15:24:39 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a9aa8895facso1114685066b.2 for ; Wed, 30 Oct 2024 08:24:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=seiler-us.20230601.gappssmtp.com; s=20230601; t=1730301870; x=1730906670; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Kr9C1IOlzWfDkBr0QZ3bAf6dBFMhXQZGlDS3V8wM5Bg=; b=PJN3OtDteOx/GzEonD5gRIdPqg2VGQT1046zlvibiNBK7KA/TFWBrykZeIWfR3FL8S POfF9ORKZyyxyQrbIaI/0M5iw/o8JlNDWIGUHeaQqRKHMiO2rSWOxicObsFJ/ou+ldNk e1AJfUCtJzEfCKPiUu62Z4TEqCtfZiOWNdHDaQ5EfH3Bst96fPYm/zW0FUwVDymHhjFw jSpUjY8R3GaNgf7KVIQXgMCRVBwkmYB2kRocchWU63B0nn2Fp7ZcU9im7H1qkWqkLinc 8K/5TTIIBEVeOUhqM8o0CDH1JHkn+ErKdiUiHUO8PwgVJcPqLA5wfGPXX2Pf7a2BCEnn nUQQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1730301870; x=1730906670; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Kr9C1IOlzWfDkBr0QZ3bAf6dBFMhXQZGlDS3V8wM5Bg=; b=w2+lW9Fpxb/ujh8ECmOMPmaVud9h4tRwYTnwPkB+luozY9BjeBnwlL13GEice9X44h WjWsvElxe3oBRceZQF8iYU4dwFwDRXsTidZ07MB8Z0SymaKc5X5AjDO0aenFWUd8Y/gn 3KCQNgp+5CL6066SB42EXh3nRPJKHHG2hKzqiT408ne+rYvrie357QToRzfgcINqRp6X QjcRy11CjZgA+fDavII0iw/NXrz4LU3uUJf5qexkN/AZjVau8zqa6poAPf78Pd6iXRFH 0g0c436fNuY1ECApfV0LTsgzufL1vEP14WKdjMGtto03BUI+u3B38/iM/SztbbV030K3 oQ/g== X-Gm-Message-State: AOJu0YyJ4xgvUal9Lp7wKYvAGCF0KVTSbp+i9CoXzeDFP2pig9mJMeZw +hjjbRu0IXoodjDDUagh881cCJfn0LZhiL8kpINDy6yapU5jq77z9lrtyvxjRAerhcSpgxJ9pe2 GRMU4sjrFupWLrz1sqTZSrAp6bx+nsAv8tlJHNkY1lwM55f+byCQ= X-Google-Smtp-Source: AGHT+IGw4BQQWKGZOaeOGuM/jwcWUOdj8vjNnW1lAJcfr/3IS8nqO6WUuJtvQ/2mVFgSdGOI6AUHRH/YTWpeh5200Kk= X-Received: by 2002:a17:907:9724:b0:a99:ec3c:15cd with SMTP id a640c23a62f3a-a9de632bf49mr1289187266b.54.1730301870464; Wed, 30 Oct 2024 08:24:30 -0700 (PDT) MIME-Version: 1.0 From: Don Seiler Date: Wed, 30 Oct 2024 10:24:18 -0500 Message-ID: Subject: Index Partition Size Double of its Table Partition? To: Postgres General Content-Type: multipart/alternative; boundary="0000000000005806e30625b34caf" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005806e30625b34caf Content-Type: text/plain; charset="UTF-8" We're trying out a new non-unique covering (including) index on a couple of table partitions. We put the index on partitions for last month and this month. Both table partitions have similar sizes (45-46 GB) and row counts (330-333 million). The covering index on last month's partition is 50GB, but this month's index is 79GB already. The table is basically write-only as well. So there shouldn't be any real bloat here. One thing worth mentioning is that the table is 4 columns, the index is on two of them and includes the other two. I can't think of an explanation for the index being so much larger than its table, especially compared to last month's index. Curious if anyone has any thoughts on what might be causing this. -- Don Seiler www.seiler.us --0000000000005806e30625b34caf Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
We're trying out a new non-unique covering (inclu= ding) index on a couple of table partitions. We put the index on partitions= for last month and this month. Both table partitions have similar sizes (4= 5-46 GB) and row counts (330-333 million). The covering index on last month= 's partition is 50GB, but this month's index is 79GB already. The t= able is basically write-only as well. So there shouldn't be any real bl= oat here.

One thing worth mentioning is that the t= able is 4 columns, the index is on two of them and includes the other two. = I can't think of an explanation for the index being so much larger than= its table, especially compared to last month's index.

Curious if anyone has any thoughts on what might be causing this.<= br>
--
Don Seiler
www.seiler.us
--0000000000005806e30625b34caf--