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) > >
