On Thu, Sep 20, 2018 at 3:41 PM Durgamahesh Manne <maheshpostgr...@gmail.com> wrote:
> 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 < > maheshpostgr...@gmail.com> wrote: > >> >> >> On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer < >> andr...@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.com >>> >>> >> hi >> >> as per your request >> >> i 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) >> >> Hi Query was executed at less time without distinct As well as query was taking around 7 minutes to complete execution with distinct 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 ; I need to execute above distinct query at less time as distinct query was taking more time to execute even i have created indexes on required columns of the tables Regards Durgamahesh Manne
