From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: John Naylor <john.naylor@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Date: Tue, 29 Sep 2020 04:12:28 +0200
Message-ID: <20200929021228.bf2vtpl3negue4y4@development> (raw)
In-Reply-To: <CACPNZCtvdOjYvYMj2y1+gbVhy_3udJR8iJRo4zkQFg=NT3_9pA@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, john.naylor@2ndquadrant.com, alvherre@2ndquadrant.com
Subject: Re: WIP: BRIN multi-range indexes
In-Reply-To: <20200929021228.bf2vtpl3negue4y4@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox