On Thu, 20 Sep 2018 at 11:42, Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> 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@xxxxxxxxx> 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@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. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
