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 <maheshpostgr...@gmail.com> wrote: > > > > > > > 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 > -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
