> Op 9 mei 2023, om 10:38 heeft Daniel Gustafsson <daniel@yesql.se> het volgende geschreven:
>
>> On 8 May 2023, at 22:12, PG Bug reporting form <noreply@postgresql.org> wrote:
>
>> The query uses a 3 partitions of a table where all fields have an brin
>> index.
>
> One index per column or a multi-colum index?
One index per column. However, if I add a multicolumn index (on ts and source) it will not crash.
>
>> This query will segfault:
>> SELECT count(*)
>> FROM dw
>> WHERE (dw.ts >= '2022-09-01' AND dw.ts <= '2022-10-30')
>> AND dw.source='type3' AND dw.customer='123.456';
>>
>> The query should return 0 because the customer does not exist.
>> Removing the dw.customer or dw.source constraint will make the segfault not
>> occur.
>> Also using 'set enable_bitmapscan to off' will not trigger the segfault.
>
> I was unable to reproduce this, can you share the table schema with the index
> creations (or a version of the schema which reproduce the problem).
>
create table if not exists dw (
activity_id bigserial,
activity text not null,
ts timestamptz not null,
customer text,
impact int default 0,
feature_1 text,
feature_2 text,
feature_3 jsonb,
link text,
source text,
source_id text,
environment text
) partition by range(ts);
CREATE INDEX IF NOT EXISTS index_activity ON dw USING brin(activity);
CREATE INDEX IF NOT EXISTS index_customer ON dw USING brin(customer);
CREATE INDEX IF NOT EXISTS index_source ON dw USING brin(source);
CREATE INDEX IF NOT EXISTS index_source_id ON dw USING brin(source_id);
CREATE INDEX IF NOT EXISTS index_ts ON dw USING brin(ts);
CREATE INDEX IF NOT EXISTS index_environment ON dw USING brin(environment);
> Have you tried recreating the indexes, in case there is corruption, or tried it
> on a table which has the same schema but no index?
It still segfaults after recreating the indexes, Without index no segfault.
>
> --
> Daniel Gustafsson
>
>
Erik.
