Re: *Regarding brin_index on required column of the table

On Thu, Sep 20, 2018 at 3:41 PM Durgamahesh Manne <maheshpostgres9(at)gmail(dot)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 <
> maheshpostgres9(at)gmail(dot)com> wrote:
>
>>
>>
>> On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <
>> andreas(at)a-kretschmer(dot)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