Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPr2V-0002ZE-5Z for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 20:27:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPr2U-0006VO-29 for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 20:27:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPr2T-0006V7-HW for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 20:27:33 +0000 Received: from mail-oo1-xc2e.google.com ([2607:f8b0:4864:20::c2e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPr2R-0001dY-8u for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 20:27:32 +0000 Received: by mail-oo1-xc2e.google.com with SMTP id z25-20020a4ad1b9000000b00517affa07c0so15073oor.7 for ; Wed, 08 Feb 2023 12:27:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=kwDLRsHNh+Ve+wc2F4zzGXlgtbmkD+vMBMO0ysbLjYA=; b=hUC1M2ndJ4oUYAiar9B3eW2SuYhVFkGgfCR1XZ18B4aGNrW6MrdNY9KWTgLKiaw/wS kL1uyQGRNjZfLryqKDAhQEp2hSoQie5vOkNF32XmkZ9gcZfU3SC1KGali/m3OQADFI2G +mDGCIaylyAwbJgSFEroKxdiYXoFKOVaPqf3hru+0grVfVXpqCv877MeiviwlQIaTY7b top9PnMBFeQ6p4AXVvJVVR/JeTwLl/X+7LQ+p/627DYInwqSSEiAoERKFqIL8s2A0Nn/ IHVYuBLlGN9yQv9EfPofZ2YV8jOMkxeq00lIe+prdVFulrlVCAAFSTnu3rXoJrtnc9Kl 0g2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=kwDLRsHNh+Ve+wc2F4zzGXlgtbmkD+vMBMO0ysbLjYA=; b=w1zwfpupXULgJSRzhtrHznI9rTZdMtpL4hMbw5QrQUG1WgDawNgMwzp1dXbgR9dczj Mrf7BQE/9Bl4YWAUcGAqYEBU8t8UvKDt8CQlFqWQzth+NZtlqeQV8XBb0BSjTj8atK+n GTo0i3o07K6C7hQ5cFh46gg2Yh0PPIMn4tffeMb9octdGlBfvdN8Uv+iU/WjygZ1NJCj xe7Qjv0QQkcNZVgaxPGt5Kqry1G36RQ3rDDNoY81XkLWJZjt3ol8nIVdwStNtmNO+rIh kUXJSOrWsSR/9MreTvfVfhlTxfNEnwWslYshHT5LBtyPmbmp2K1KdSuURBmi6QJ2dXyN Xkrw== X-Gm-Message-State: AO0yUKV4veY6ULad0MHLTpRY5wVEBaDt/6uLpfvxehaunHXTpWsmOcth qZLusdK5k+yWOL0mdLnTXaQYcp0hGuM= X-Google-Smtp-Source: AK7set9wgJMRVX37RQzajOD7n7W60foMzOdtapJByHI9wr0SfmtVrPaTfmb4GC/WC3PAzF4wo9Kxhg== X-Received: by 2002:a4a:87:0:b0:517:5a19:a10b with SMTP id 129-20020a4a0087000000b005175a19a10bmr3816354ooh.5.1675888050366; Wed, 08 Feb 2023 12:27:30 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id w1-20020a4ae9e1000000b0051ac0f54447sm909763ooc.33.2023.02.08.12.27.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 08 Feb 2023 12:27:29 -0800 (PST) Content-Type: multipart/alternative; boundary="------------K0AoRa3mW18eg4ODOSAJuzQz" Message-ID: Date: Wed, 8 Feb 2023 14:27:29 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: How to use the BRIN index properly? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------K0AoRa3mW18eg4ODOSAJuzQz Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Is the data in your tables stored in natural correlation with those *three* columns?  I'm dubious that can even happen. BRIN is best for *range queries* on tables who's data is added in the same order as the key in the BRIN index (for example, a BRIN index on a timestamp field in a log table where new records are always being appended in "timestamp" order). It would also be great for history tables where you can pre-sort the data by, for example, customer_id, and then put the BRIN on customer_id. On 2/8/23 13:58, Siddharth Jain wrote: > our insertion order is of course != index order otherwise the question > would have been trivial. > we use postgres 14 > > On Wed, Feb 8, 2023 at 11:51 AM Siddharth Jain wrote: > > Hello, > > We have large tables with billions of rows in them and want to take > advantage of the BRIN index on them. > > Issues we are facing: > > * as I understand, BRIN index is useful only if the data is stored > in index order. As an example we want to create a composite BRIN > index on 3 columns - integers and strings (varchar). How can we > tell Postgres to store data in index order as new records are > inserted into the database? > * i understand that turning on autosummarize will keep the index > fresh and up-to-date as new records are inserted. is this correct? > > Thanks for your help. > > S. > -- Born in Arizona, moved to Babylonia. --------------K0AoRa3mW18eg4ODOSAJuzQz Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Is the data in your tables stored in natural correlation with those three columns?  I'm dubious that can even happen.

BRIN is best for range queries on tables who's data is added in the same order as the key in the BRIN index (for example, a BRIN index on a timestamp field in a log table where new records are always being appended in "timestamp" order).

It would also be great for history tables where you can pre-sort the data by, for example, customer_id, and then put the BRIN on customer_id.

On 2/8/23 13:58, Siddharth Jain wrote:
our insertion order is of course != index order otherwise the question would have been trivial.
we use postgres 14

On Wed, Feb 8, 2023 at 11:51 AM Siddharth Jain <siddhsql@gmail.com> wrote:
Hello,

We have large tables with billions of rows in them and want to take advantage of the BRIN index on them.

Issues we are facing:
  • as I understand, BRIN index is useful only if the data is stored in index order. As an example we want to create a composite BRIN index on 3 columns - integers and strings (varchar). How can we tell Postgres to store data in index order as new records are inserted into the database?
  • i understand that turning on autosummarize will keep the index fresh and up-to-date as new records are inserted. is this correct?
Thanks for your help.

S.

--
Born in Arizona, moved to Babylonia.
--------------K0AoRa3mW18eg4ODOSAJuzQz--