Date: Tue, 29 Sep 2020 04:12:28 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: John Naylor <john.naylor@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Message-ID: <20200929021228.bf2vtpl3negue4y4@development>
References: 
 <CACPNZCuqpkCGt8=cywAk1kPu0OoV_TjPXeV-J639ABQWyViyug@mail.gmail.com>
 <CACPNZCsO1SXkVaECWVQ9C0GvT9qq13DR+5EbrBDSRT4rJFa8qQ@mail.gmail.com>
 <20200918114014.sz5a3nwt7zont4rz@development>
 <CACPNZCvjt_Td2q3OqZdo_qZUjX_j+9Gotru1eEe4UVq02fLM+A@mail.gmail.com>
 <20200918222702.omsieaphfj3ctqg3@development>
 <CACPNZCsnUJjGbC6pMWj0CZF9w2rRcjjohX+6X2xU5+nQesiJ8w@mail.gmail.com>
 <20200921195607.gt6xbia7v55bhuwq@development>
 <CACPNZCvvq9W8tbE4PQbyPJyLXWnJCPTJ099LTWrbvb5NDs86qg@mail.gmail.com>
 <20200924235045.bgxsilvf7askukph@development>
 <CACPNZCtvdOjYvYMj2y1+gbVhy_3udJR8iJRo4zkQFg=NT3_9pA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CACPNZCtvdOjYvYMj2y1+gbVhy_3udJR8iJRo4zkQFg=NT3_9pA@mail.gmail.com>
Precedence: bulk

On Mon, Sep 28, 2020 at 04:42:39PM -0400, John Naylor wrote:
>On Thu, Sep 24, 2020 at 7:50 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>>
>> On Thu, Sep 24, 2020 at 05:18:03PM -0400, John Naylor wrote:
>
>> >Hmm, how ugly would it be to change the default range size depending
>> >on the opclass?
>> >
>>
>> Not sure. What would happen for multi-column BRIN indexes with different
>> opclasses?
>
>Sounds like a can of worms. In any case I suspect if there is no more
>graceful way to handle too-large filters than ERROR out the first time
>trying to write to the index, this feature might meet some resistance.
>Not sure what to suggest, though.
>

Is it actually all that different from the existing BRIN indexes?
Consider this example:

create table x (a text, b text, c text);

create index on x using brin (a,b,c);

create or replace function random_str(p_len int) returns text as $$
select string_agg(x, '') from (select chr(1 + (254 * random())::int ) as x from generate_series(1,$1)) foo;
$$ language sql;

test=# insert into x select random_str(1000), random_str(1000), random_str(1000);
ERROR:  index row size 9056 exceeds maximum 8152 for index "x_a_b_c_idx"


I'm a bit puzzled, though, because both of these things seem to work:

1) insert before creating the index

create table x (a text, b text, c text);
insert into x select random_str(1000), random_str(1000), random_str(1000);
create index on x using brin (a,b,c);
-- and there actually is a non-empty summary with real data
select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2), 'x_a_b_c_idx'::regclass);


2) insert "small" row before inserting the over-sized one

create table x (a text, b text, c text);
insert into x select random_str(10), random_str(10), random_str(10);
insert into x select random_str(1000), random_str(1000), random_str(1000);
create index on x using brin (a,b,c);
-- and there actually is a non-empty summary with the "big" values
select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2), 'x_a_b_c_idx'::regclass);


I find this somewhat strange - how come we don't fail here too?


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services