From: Durgamahesh Manne [mailto:maheshpostgres9@xxxxxxxxx]
Sent: Wednesday, September 19, 2018 10:04 AM
To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>
Subject: Re: Regrading brin_index on required column of the tableOn Wed, Sep 19, 2018 at 7:19 PM Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
From: Durgamahesh Manne [mailto:maheshpostgres9@xxxxxxxxx]
Sent: Wednesday, September 19, 2018 9:43 AM
To: PostgreSQL mailing lists <pgsql-general@xxxxxxxxxxxxxx>
Subject: Regrading brin_index on required column of the tableHi
Respected postgres community members
I have created BRIN index on few columns of the table without any issues. But i am unable to create BRIN index on one column of the table as i got error listed below
[local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using brin ("dFetch");
ERROR: data type boolean has no default operator class for access method "brin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
below is the column description:
Column datatype collation nullable default storage
dFetch boolean false plain
so please help in creating of the BRIN index on above column of the table .
Regards
Durgamahesh Manne
Why would you want BRIN index on Boolean-type column?
What kind of interval will you specify?
Regards,
Igor Neyman
Hi
I have complex query like for ex select distinct sub_head."vchSubmittersCode" ,rec."vchFileName" , rec."vchCusipFundIDSubFundID" , rec."vchFundUnitPrice" , sub_head."vchValuationDate" ,rec."vchAdvisorLabel" from "table1" rec join "table2" sub_head on rec."vchSubmittersCode"=sub_head."vchSubmittersCode" where rec."bFetch"=false and sub_head."bFetch"=false ;
Query taken around 7 minutes time to execute without indexes on required columns
SO i need to execute this distinct query at less time by creating indexes on required columns of the tables
i have created brin indexes on vchsubmitterscode of two tables
i am not able to create brin indexes on bfetch tables as i got a error ERROR: data type boolean has no default operator class for access method "brin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
so please help in creating of the BRIN index on above column of the table as i need to reduce the query execution time
Regards
Durgamahesh Manne
Again, BRIN indexes are not design to work on Boolean columns. If you want to index Boolean column, just create regular BTREE index.
Regards,
Igor
Query taken around 7 minutes time to execute with BTREE indexes & HASH indexes on required columns