On Wed, Sep 19, 2018 at 7:41 PM Igor Neyman <iney...@perceptron.com> wrote:
> > > *From:* Durgamahesh Manne [mailto:maheshpostgr...@gmail.com] > *Sent:* Wednesday, September 19, 2018 10:04 AM > *To:* Igor Neyman <iney...@perceptron.com> > *Subject:* Re: Regrading brin_index on required column of the table > > On Wed, Sep 19, 2018 at 7:19 PM Igor Neyman <iney...@perceptron.com> > wrote: > > > > *From:* Durgamahesh Manne [mailto:maheshpostgr...@gmail.com] > *Sent:* Wednesday, September 19, 2018 9:43 AM > *To:* PostgreSQL mailing lists <pgsql-gene...@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 > > > > > Hi I have already tried with BTREE indexes & HASH indexes on required columns .but distinct query execution time was not reduced Query taken around 7 minutes time to execute with BTREE indexes & HASH indexes on required columns Regards Durgamahesh Manne
