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@xxxxxxxxx> wrote: > > > > > > > On Wed, Sep 19, 2018 at 7:41 PM Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote: >> >> >> >> 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 table >> >> On 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 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 > -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
