On Thu, Sep 20, 2018 at 6:39 PM Andreas Kretschmer <andr...@a-kretschmer.de> wrote:
> > > Am 20.09.2018 um 13:11 schrieb Durgamahesh Manne: > > 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 > > > > > 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 | > > > as you can see: there are 40.500.000 rows to sort to filter out > duplicate rows, the result contains 'only' 3.700.000 rows. But for this > step the database needs nearly 4TB on-disk. This will, of course, need > some time. > > If you have enough ram you can try to set work_mem to 5 or 6 GB to > change the plan to a in-memory - sort. But keep in mind, this is > dangerous! If the machine don't have enough free ram the kernal can > decide to Out-Of-Memory - killing processes. > > What kind of disks do you have? Maybe you can use a separate fast SSD as > temp_tablespaces? > > > Regards, Andreas > -- > > 2ndQuadrant - The PostgreSQL Support Company. > www.2ndQuadrant.com > > > Hi sdb[HDD] sdc[HDD] sda[HDD] i checked that there are hdd's in linux Regards
