On Wed, Sep 19, 2018 at 8:27 PM Andreas Kretschmer <andr...@a-kretschmer.de> wrote:
> > > Am 19.09.2018 um 16:43 schrieb Durgamahesh Manne: > > > > > > On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer > > <andr...@a-kretschmer.de <mailto:andr...@a-kretschmer.de>> wrote: > > > > > > > > Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne: > > > 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. > > > > honestly, a BRIN-Index on a bool-column doesn't make much sense. > > What do > > you want to achive? Maybe a partial index with a where-condition > > on that > > column makes much more sense. > > > > > > Regards, Andreas > > > > -- > > 2ndQuadrant - The PostgreSQL Support Company. > > www.2ndQuadrant.com <http://www.2ndQuadrant.com> > > > > > > > > Hi > > > > > > I want to execute distinct query at less possible time > > > > for that reason ,Even i have already tried with BTREE indexes & HASH > > indexes on required columns .distinct query execution time was not > reduced > > > > 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 with BTREE indexes & HASH > > indexes on required columns > > > > try an index like > > create index idx_t1 on table1 ("vchSubmittersCode") where "bFetch"=false > and > create index idx_t2 on table2 ("vchSubmittersCode") where "bFetch"=false > > and check if the plan changed and the indexes are in use. You can use > create index concurrently to prevent lockings. > > > > Regards, Andreas > > -- > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > Hi As per your suggestion i have created partial indexes with where condition on required columns distinct query execution time was not reduced as query taken around 7 minutes time to execute with indexes & without indexes so i ran explain analyze for distinct query EXPLAIN ANALYZE 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 ; Unique (cost=5871873.64..6580630.73 rows=7400074 width=89) (actual time=326397.551..389515.863 rows=3700000 loops=1) | | -> 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 | | -> Hash Join (cost=21.06..457723.28 rows=40500405 width=89) (actual time=0.339..6939.296 rows=40500000 loops=1) | | Hash Cond: ((rec."vchSubmittersCode")::text = (sub_head."vchSubmittersCode")::text) | | -> Seq Scan on table1 rec (cost=0.00..1822.66 rows=100001 width=80) (actual time=0.011..56.998 rows=100000 loops=1) | | Filter: (NOT "bFetch") | | Rows Removed by Filter: 4706 | | -> Hash (cost=16.00..16.00 rows=405 width=11) (actual time=0.319..0.319 rows=405 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 26kB | | -> Seq Scan on table2 sub_head (cost=0.00..16.00 rows=405 width=11) (actual time=0.005..0.248 rows=405 loops=1) | | Filter: (NOT "bFetch") | | Rows Removed by Filter: 375 | | Planning time: 0.237 ms | | Execution time: 390252.089 ms so please help in reducing the distinct query execution time Regrads Durgamahesh Manne
