On Wed, Sep 19, 2018 at 8:27 PM Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx> wrote:
Am 19.09.2018 um 16:43 schrieb Durgamahesh Manne:
>
>
> On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer
> <andreas@xxxxxxxxxxxxxxx <mailto:andreas@xxxxxxxxxxxxxxx>> 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
