Re: *Regarding brin_index on required column of the table - Mailing list pgsql-general
| From | Durgamahesh Manne |
|---|---|
| Subject | Re: *Regarding brin_index on required column of the table |
| Date | |
| Msg-id | CAJCZkoL0Lq3MB2xkBmHD-MGh+=+yV842TEmYyKo4Y++Zp_zBLw@mail.gmail.com Whole thread Raw |
| In response to | Re: *Regarding brin_index on required column of the table (Durgamahesh Manne <maheshpostgres9@gmail.com>) |
| Responses |
Re: *Regarding brin_index on required column of the table
(Durgamahesh Manne <maheshpostgres9@gmail.com>)
|
| List | pgsql-general |
hi
as per your request
i ran below query without distinct
select 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 ;
the above query took around 47 sec to execute
the above query took around 7 minutes to execute with distinct
On Thu, Sep 20, 2018 at 3:28 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <andreas@a-kretschmer.de> wrote:Hi,
the problem is there:
Am 20.09.2018 um 11:48 schrieb Durgamahesh Manne:
> 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
> |
Please check the execution time without DISTINCT.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.comhias per your requesti ran explain analyze query without distinct+------------------------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+------------------------------------------------------------------------------------------------------------------------------------------------------+| Hash Join (cost=21.06..457723.28 rows=40500405 width=89) (actual time=0.429..6763.942 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.006..48.610 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.404..0.404 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.004..0.326 rows=405 loops=1) || Filter: (NOT "bFetch") || Rows Removed by Filter: 375 || Planning time: 0.351 ms || Execution time: 8371.819 ms |+------------------------------------------------------------------------------------------------------------------------------------------------------+(12 rows)
pgsql-general by date: