Re: WIP: BRIN multi-range indexes
От | Tomas Vondra |
---|---|
Тема | Re: WIP: BRIN multi-range indexes |
Дата | |
Msg-id | 20201001184133.oq5uq75sb45pu3aw@development обсуждение исходный текст |
Ответ на | Re: WIP: BRIN multi-range indexes (John Naylor <john.naylor@2ndquadrant.com>) |
Ответы |
Re: WIP: BRIN multi-range indexes
|
Список | pgsql-hackers |
On Wed, Sep 30, 2020 at 07:57:19AM -0400, John Naylor wrote: >On Mon, Sep 28, 2020 at 10:12 PM Tomas Vondra ><tomas.vondra@2ndquadrant.com> wrote: > >> 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" > >Hmm, okay. As for which comes first, insert or index creation, I'm >baffled, too. I also would expect the example above would take up a >bit over 6000 bytes, but not 9000. > OK, so this seems like a data corruption bug in BRIN, actually. The ~9000 bytes is actually about right, because the strings are in UTF-8 so roughly 1.5B per character seems about right. And we have 6 values to store (3 columns, min/max for each), so 6 * 1500 = 9000. The real question is how come INSERT + CREATE INDEX actually manages to create an index tuple. And the answer is pretty simple - brin_form_tuple kinda ignores toasting, happily building index tuples where some values are toasted. Consider this: 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); delete from x; vacuum x; set enable_seqscan=off; insert into x select random_str(10), random_str(10), random_str(10); ERROR: missing chunk number 0 for toast value 16530 in pg_toast_16525 explain analyze select * from x where a = 'xxx'; ERROR: missing chunk number 0 for toast value 16530 in pg_toast_16525 select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2), 'x_a_b_c_idx'::regclass); ERROR: missing chunk number 0 for toast value 16547 in pg_toast_16541 Interestingly enough, running the select before the insert seems to be working - not sure why. Anyway, it behaves like this since 9.5 :-( regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-hackers по дате отправления: