Re: *Regarding brin_index on required column of the table

On Thu, Sep 20, 2018 at 6:39 PM Andreas Kretschmer <andreas(at)a-kretschmer(dot)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