Re: Regrading brin_index on required column of the table - Mailing list pgsql-general

 

From: Durgamahesh Manne [mailto:maheshpostgres9@gmail.com]
Sent: Wednesday, September 19, 2018 10:04 AM
To: Igor Neyman <ineyman@perceptron.com>
Subject: Re: Regrading brin_index on required column of the table

On Wed, Sep 19, 2018 at 7:19 PM Igor Neyman <ineyman@perceptron.com> wrote:

 

From: Durgamahesh Manne [mailto:maheshpostgres9@gmail.com]
Sent: Wednesday, September 19, 2018 9:43 AM
To: PostgreSQL mailing lists <pgsql-general@postgresql.org>
Subject: Regrading brin_index on required column of the table

Hi 

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