Re: Regrading brin_index on required column of the table

On Wed, Sep 19, 2018 at 7:41 PM Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

>
>
> *From:* Durgamahesh Manne [mailto:maheshpostgres9(at)gmail(dot)com]
> *Sent:* Wednesday, September 19, 2018 10:04 AM
> *To:* Igor Neyman <ineyman(at)perceptron(dot)com>
> *Subject:* Re: Regrading brin_index on required column of the table
>
> On Wed, Sep 19, 2018 at 7:19 PM Igor Neyman <ineyman(at)perceptron(dot)com>
> wrote:
>
>
>
> *From:* Durgamahesh Manne [mailto:maheshpostgres9(at)gmail(dot)com]
> *Sent:* Wednesday, September 19, 2018 9:43 AM
> *To:* PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)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(at)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