Thread: Regrading brin_index on required column of the table

The results of explain analyze would shed light on the core problem.

My guess is that your conditions are not very selective - ie. most
records in both tables have bFetch = false - and therefore you are
retrieving most of your data and that is what's taking 7 minutes. No
index is going to fix that.

If those boolean values are distributed very unevenly (say 99.9% has
false and 0.1% has true), you may get better results by excluding the
records with 'true' values (instead of including those that are
'false'), for example by using a where not exists(...) subquery.

Obviously, that still won't help if you're just fetching a lot of data.
On Wed, 19 Sep 2018 at 16:23, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
>
>
>
>
>
>
> On Wed, Sep 19, 2018 at 7:41 PM Igor Neyman <ineyman@perceptron.com> wrote:
>>
>>
>>
>> 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
onecolumn 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
andsub_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
operatorclass 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
regularBTREE 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
>


--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Thu, 20 Sep 2018 at 11:42, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:

...

> |   ->  Sort  (cost=5871873.64..5973124.65 rows=40500405 width=89) (actual time=326397.550..372470.846 rows=40500000
loops=1)                                                 | 
> |         Sort Key: sub_head."vchSubmittersCode", rec."vchFileName", rec."vchCusipFundIDSubFundID",
rec."vchFundUnitPrice",sub_head."vchValuationDate", rec."vchAdvisorLabel" | 
> |         Sort Method: external merge  Disk: 3923224kB
                                                        | 
The above is a clear sign of a problem.
To get distinct records, the results need to be sorted, and that
doesn't fit in the available memory and spills to disk.

The actual filters on the boolean fields, even though they are
performed in seq-scans, hardly take any time at all. The hash join to
combine them takes a bit over 6s.

> so i am unable to reduce the query execution time as it is taken around 7 minutes to execute with indexes & without
indexes
>
>  please help in reducing the query execution time

...

> On Wed, Sep 19, 2018 at 9:07 PM Alban Hertroys <haramrae@gmail.com> wrote:
>>
>> The results of explain analyze would shed light on the core problem.
>>
>> My guess is that your conditions are not very selective - ie. most
>> records in both tables have bFetch = false - and therefore you are
>> retrieving most of your data and that is what's taking 7 minutes. No
>> index is going to fix that.
>>
>> If those boolean values are distributed very unevenly (say 99.9% has
>> false and 0.1% has true), you may get better results by excluding the
>> records with 'true' values (instead of including those that are
>> 'false'), for example by using a where not exists(...) subquery.
>>
>> Obviously, that still won't help if you're just fetching a lot of data.
>> On Wed, 19 Sep 2018 at 16:23, Durgamahesh Manne
>> <maheshpostgres9@gmail.com> wrote:
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Wed, Sep 19, 2018 at 7:41 PM Igor Neyman <ineyman@perceptron.com> wrote:
>> >>
>> >>
>> >>
>> >> 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
onecolumn 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
andsub_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
operatorclass 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
regularBTREE index. 
>> >>
>> >> Regards,
>> >>
>> >> Igor
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >  Hi
>> >
>> > I have already tried with BTREE indexes & HASH indexes on required columns .but distinct query execution time was
notreduced 
>> >
>> >
>> > Query taken around 7 minutes time to execute with BTREE indexes & HASH indexes on required columns
>> >
>> >
>> > Regards
>> >
>> > Durgamahesh Manne
>> >
>>
>>
>> --
>> If you can't see the forest for the trees,
>> Cut the trees and you'll see there is no forest.



--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.