Обсуждение: *Regarding brin_index on required column of the table

Поиск

Список

Период

Сортировка

*Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

19 сентября 2018 г., 19:51:13

Respected postgres community members

I have created BRIN index on few columns of the table without any issues. But i am unable to create BRIN index on one column of the table as i got error listed below

[local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using brin ("dFetch");

ERROR: data type boolean has no default operator class for access method "brin"

HINT: You must specify an operator class for the index or define a default operator class for the data type.

below is the column description:

Column datatype collation nullable default storage

dFetch boolean false plain

so please help in creating of the BRIN index on above column of the table .

Regards

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

19 сентября 2018 г., 20:31:45


Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne:
> I have created BRIN index on few columns of the table without any 
> issues. But i am unable to create BRIN index on one column of the 
> table as i got error listed below
>
>
> [local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using 
> brin ("dFetch");
> ERROR:  data type boolean has no default operator class for access 
> method "brin"
> HINT:  You must specify an operator class for the index or define a 
> default operator class for the data type.

honestly, a BRIN-Index on a bool-column doesn't make much sense. What do 
you want to achive? Maybe a partial index with a where-condition on that 
column makes much more sense.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

19 сентября 2018 г., 20:32:19

I have complex query like for ex 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 ;

Even i have already tried with BTREE indexes & HASH indexes on required columns .distinct query execution time was not reduced

Query taken around 7 minutes time to execute with BTREE indexes & HASH indexes on required columns

SO please help in reducing the distinct query execution time

Regards

Durgamahesh Manne

On Wed, Sep 19, 2018 at 7:21 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:

Hi
Respected postgres community members

I have created BRIN index on few columns of the table without any issues. But i am unable to create BRIN index on one column of the table as i got error listed below

[local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using brin ("dFetch");
ERROR: data type boolean has no default operator class for access method "brin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

below is the column description:
Column datatype collation nullable default storage

dFetch boolean false plain

so please help in creating of the BRIN index on above column of the table .

Regards

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

19 сентября 2018 г., 20:43:09

On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne:
> I have created BRIN index on few columns of the table without any
> issues. But i am unable to create BRIN index on one column of the
> table as i got error listed below
>
>
> [local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using
> brin ("dFetch");
> ERROR: data type boolean has no default operator class for access
> method "brin"
> HINT: You must specify an operator class for the index or define a
> default operator class for the data type.

honestly, a BRIN-Index on a bool-column doesn't make much sense. What do
you want to achive? Maybe a partial index with a where-condition on that
column makes much more sense.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

I want to execute distinct query at less possible time

for that reason ,Even i have already tried with BTREE indexes & HASH indexes on required columns .distinct query execution time was not reduced

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 ;

Query taken around 7 minutes time to execute with BTREE indexes & HASH indexes on required columns

Regards

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

19 сентября 2018 г., 20:56:56


Am 19.09.2018 um 16:43 schrieb Durgamahesh Manne:
>
>
> On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer 
> <andreas@a-kretschmer.de <mailto:andreas@a-kretschmer.de>> wrote:
>
>
>
>     Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne:
>     > I have created BRIN index on few columns of the table without any
>     > issues. But i am unable to create BRIN index on one column of the
>     > table as i got error listed below
>     >
>     >
>     > [local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei
>     using
>     > brin ("dFetch");
>     > ERROR:  data type boolean has no default operator class for access
>     > method "brin"
>     > HINT:  You must specify an operator class for the index or define a
>     > default operator class for the data type.
>
>     honestly, a BRIN-Index on a bool-column doesn't make much sense.
>     What do
>     you want to achive? Maybe a partial index with a where-condition
>     on that
>     column makes much more sense.
>
>
>     Regards, Andreas
>
>     -- 
>     2ndQuadrant - The PostgreSQL Support Company.
>     www.2ndQuadrant.com <http://www.2ndQuadrant.com>
>
>
>
> Hi
>
>
> I want to execute distinct query at less possible time
>
> for that reason ,Even i have already tried with BTREE indexes & HASH 
> indexes on required columns .distinct query execution time was not reduced
>
> 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 ;
>
> Query taken around 7 minutes time to execute with BTREE indexes & HASH 
> indexes on required columns
>

try an index like

create index idx_t1 on table1 ("vchSubmittersCode") where "bFetch"=false
and
create index idx_t2 on table2 ("vchSubmittersCode") where "bFetch"=false

and check if the plan changed and the indexes are in use. You can use 
create index concurrently to prevent lockings.



Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 15:48:55

On Wed, Sep 19, 2018 at 8:27 PM Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Am 19.09.2018 um 16:43 schrieb Durgamahesh Manne:
>
>
> On Wed, Sep 19, 2018 at 8:02 PM Andreas Kretschmer
> <andreas@a-kretschmer.de <mailto:andreas@a-kretschmer.de>> wrote:
>
>
>
> Am 19.09.2018 um 15:51 schrieb Durgamahesh Manne:
> > I have created BRIN index on few columns of the table without any
> > issues. But i am unable to create BRIN index on one column of the
> > table as i got error listed below
> >
> >
> > [local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei
> using
> > brin ("dFetch");
> > ERROR: data type boolean has no default operator class for access
> > method "brin"
> > HINT: You must specify an operator class for the index or define a
> > default operator class for the data type.
>
> honestly, a BRIN-Index on a bool-column doesn't make much sense.
> What do
> you want to achive? Maybe a partial index with a where-condition
> on that
> column makes much more sense.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com <http://www.2ndQuadrant.com>
>
>
>
> Hi
>
>
> I want to execute distinct query at less possible time
>
> for that reason ,Even i have already tried with BTREE indexes & HASH
> indexes on required columns .distinct query execution time was not reduced
>
> 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 ;
>
> Query taken around 7 minutes time to execute with BTREE indexes & HASH
> indexes on required columns
>

try an index like

create index idx_t1 on table1 ("vchSubmittersCode") where "bFetch"=false
and
create index idx_t2 on table2 ("vchSubmittersCode") where "bFetch"=false

and check if the plan changed and the indexes are in use. You can use
create index concurrently to prevent lockings.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

As per your suggestion

i have created partial indexes with where condition on required columns

distinct query execution time was not reduced as query taken around 7 minutes time to execute with indexes & without indexes

so i ran explain analyze for distinct query

EXPLAIN ANALYZE 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 ;

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 |

| -> Hash Join (cost=21.06..457723.28 rows=40500405 width=89) (actual time=0.339..6939.296 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.011..56.998 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.319..0.319 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.005..0.248 rows=405 loops=1) |

| Filter: (NOT "bFetch") |

| Rows Removed by Filter: 375 |

| Planning time: 0.237 ms |

| Execution time: 390252.089 ms

so please help in reducing the distinct query execution time

Regrads

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

20 сентября 2018 г., 15:52:55

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

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 15:58:46

On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <andreas@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

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)

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 16:11:46

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@gmail.com> wrote:

On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <andreas@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)

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 17:11:35

On Thu, Sep 20, 2018 at 3:41 PM Durgamahesh Manne <maheshpostgres9@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 <maheshpostgres9@gmail.com> wrote:

On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <andreas@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)

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

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

20 сентября 2018 г., 19:08:32


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

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 19:55:21

On Thu, Sep 20, 2018 at 6:39 PM Andreas Kretschmer <andreas@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

sdb[HDD]

sdc[HDD]

sda[HDD]

i checked that there are hdd's in linux

Regards

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

20 сентября 2018 г., 20:23:48

On Thu, Sep 20, 2018 at 7:25 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:

On Thu, Sep 20, 2018 at 6:39 PM Andreas Kretschmer <andreas@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

distinct query executed very fast as i have increased work_mem value to 3gb temporarily

Thank you very much for this valuable information

now i would like to ask one question related to built in bdr replication

when can be available bdr built in replication for use in production

can i use v3 built in replication in prod?

please let me know about the configuration of v3 bdr built in replication

Regards

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

21 сентября 2018 г., 20:08:58

Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gb

Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columns

Below is the query plan for explain analyze query :

| HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |

| Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |

| -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |

| -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |

| -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |

| Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |

| Sort Method: quicksort Memory: 3366kB |

| -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |

| Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |

| -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |

| Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |

| Sort Method: quicksort Memory: 241964kB |

| -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) |

| Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) |

| -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual time=0.030..3.283 rows=4200 loops=1) |

| -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) |

| -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual time=0.005..81.432 rows=125522 loops=1) |

| -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 loops=1) |

| -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) |

| -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) |

| Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole", (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)), (max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)), (max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)), (max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier", (max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)), (max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)), (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)), (max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)), (max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)), (max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)), j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k."vchAgentMiddleName")::text))) || ' '::text) || btrim(max((k."vchAgentLastName")::text)))) |

| Sort Method: quicksort Memory: 23482kB |

| -> GroupAggregate (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual time=847482.207..1802617.045 rows=22461 loops=1) |

| Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |

| -> Sort (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual time=847464.789..1486679.680 rows=61595746 loops=1) |

| Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |

| Sort Method: external merge Disk: 42758304kB |

| -> Nested Loop (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837 rows=61595746 loops=1) |

| -> Seq Scan on "table3" j (cost=0.00..669.12 rows=25132 width=591) (actual time=0.021..28.338 rows=25132 loops=1) |

| Filter: (NOT "bFetch") |

| -> Index Scan using cpr_idx4 on table2 k (cost=0.42..6.92 rows=365 width=107) (actual time=0.838..2.244 rows=2451 loops=25132) |

| Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) |

| Planning time: 2.369 ms |

| Execution time: 1807771.091 ms

So i need to execute below query at less time. please help in
optimising the complex query execution time

Regards

Durgamahesh Manne

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

21 сентября 2018 г., 20:14:16

On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:

Hi

Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gb

Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columns

Below is the query plan for explain analyze query :

| HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |
| Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
| -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |
| -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |
| -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |
| Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
| Sort Method: quicksort Memory: 3366kB |
| -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |
| Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
| -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |
| Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
| Sort Method: quicksort Memory: 241964kB |
| -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) |
| Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) |
| -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual time=0.030..3.283 rows=4200 loops=1) |
| -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) |
| -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual time=0.005..81.432 rows=125522 loops=1) |
| -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 loops=1) |
| -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) |
| -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) |
| Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole", (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)), (max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)), (max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)), (max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier", (max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)), (max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)), (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)), (max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)), (max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)), (max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)), j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k."vchAgentMiddleName")::text))) || ' '::text) || btrim(max((k."vchAgentLastName")::text)))) |
| Sort Method: quicksort Memory: 23482kB |
| -> GroupAggregate (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual time=847482.207..1802617.045 rows=22461 loops=1) |
| Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |
| -> Sort (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual time=847464.789..1486679.680 rows=61595746 loops=1) |
| Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |
| Sort Method: external merge Disk: 42758304kB |
| -> Nested Loop (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837 rows=61595746 loops=1) |
| -> Seq Scan on "table3" j (cost=0.00..669.12 rows=25132 width=591) (actual time=0.021..28.338 rows=25132 loops=1) |
| Filter: (NOT "bFetch") |
| -> Index Scan using cpr_idx4 on table2 k (cost=0.42..6.92 rows=365 width=107) (actual time=0.838..2.244 rows=2451 loops=25132) |
| Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) |
| Planning time: 2.369 ms |
| Execution time: 1807771.091 ms

So i need to execute below query at less time. please help in
optimising the complex query execution time

Regards

Durgamahesh Manne

So i need to execute below query at less time as i just sent query plan to mailing list

please help in optimising the complex query execution time

SELECT distinct Max(v."vchSubmittersCode") as vchSubmittersCode , Max(v."vchRecordType") as vchRecordType , Max(v."vchSequenceNumber") as vchSequenceNumber , v."vchContractNumber" ,"vchContractPartyRoleQualifier" ,"vchPartyRole" ,Max("vchPartyNatural_Non_NaturalEntity") as vchPartyNatural_Non_NaturalEntity , Max("vchPartyLastName") as vchPartyLastName ,Max("vchPartyFirstName") as vchPartyFirstName,Max("vchPartyMiddleName") as vchPartyMiddleName, Max("vchPartyPrefix") as vchPartyPrefix ,Max("vchPartySuffix") as vchPartySuffix, NULL "vchContractEntityE_mailAddress", "vchPartyID" , Max("vchPartyIDQualifier") as vchPartyIDQualifier ,Max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator,NULL "vchContractEntityPhoneNumber",NULL "vchContractEntityPhoneExtension",Max(v."vchFiller1") as vchFiller1,Max(v."vchRejectCode") as vchRejectCode, Max("vchContractEntityAddressLine1") as vchContractEntityAddressLine1, Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2, Max("vchContractEntityCity") as vchContractEntityCity, Max("vchContractEntityState") as vchContractEntityState, Max("vchContractEntityZip") as vchContractEntityZip, Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3, Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4, Max("vchContractEntityAddressLine5") as vchContractEntityAddressLine5 ,Max("vchPartyDateofBirth") as vchPartyDateofBirth, Max("vchPartyAddressLine1") as vchPartyAddressLine1, Max("vchContractStatus") as vchContractStatus, string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID , "vchPartyRole" ,Max(v."vchAdvisorLabel") as vchAdvisorLabel,v."vchFileName",Max("vchpartycity") as vchpartycity,Max("vchpartystate") as vchpartystate,Max("vchpartypostalcode") as vchpartypostalcode ,string_agg(distinct trim(s."vchAgentFirstName")||' '||trim(s."vchAgentMiddleName")||' '||trim(s."vchAgentLastName"),',') as "AgentName" FROM TABLE1 as v join"DTCC".TABLE2 AS s on v."vchContractNumber" = s."vchContractNumber" where v."bFetch" = false GROUP BY "vchPartyRole",v."vchFileName","vchPartyID" ,"vchPartyRole" ,"vchContractPartyRoleQualifier" , v."vchContractNumber" UNION SELECT distinct max(j."vchSubmittersCode") as vchSubmittersCode,max(j."vchRecordType") as vchRecordType,max(j."vchSequenceNumber") as vchSequenceNumber , j."vchContractNumber" , max("vchContractEntityTypeCode") as vchContractEntityTypeCode,"vchContractEntityRole" ,max("vchContractEntityNatural_Non_NaturalNameIndicator") as vchContractEntityNatural_Non_NaturalNameIndicator ,max("vchContractEntityLastName") as vchContractEntityLastName , max("vchContractEntityFirstName") as vchContractEntityFirstName , max("vchContractEntityMiddleName") as vchContractEntityMiddleName , max("vchContractEntityPrefix") as vchContractEntityPrefix , max("vchContractEntitySuffix") as vchContractEntitySuffix, max("vchContractEntityE_mailAddress") as vchContractEntityE_mailAddress, "vchContractEntityPersonalIdentifier" , max("vchContractEntityPersonalQualifier") as vchContractEntityPersonalQualifier, max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber, max("vchContractEntityPhoneExtension") as vchContractEntityPhoneExtension, max(j."vchFiller1") as vchFiller1, max(j."vchRejectCode") as vchRejectCode, max("vchcontractentityaddressline1") as vchcontractentityaddressline1 ,max("vchcontractentityaddressline2") as vchcontractentityaddressline2,max("vchcontractentitycity") as vchcontractentitycity, max("vchcontractentitystate") as vchcontractentitystate,max("vchcontractentityzip") as vchcontractentityzip, max("vchcontractentityaddressline3") as vchcontractentityaddressline3,max("vchcontractentityaddressline4") as vchcontractentityaddressline4,max("vchcontractentityaddressline5") as vchcontractentityaddressline5, NULL "vchPartyDateofBirth", NULL "vchPartyAddressLine1", NULL "vchContractStatus", string_agg(distinct trim(j."vchagenttaxid"),',') as vchagenttaxid, "vchContractEntityRole" , max(j."vchAdvisorLabel") as vchAdvisorLabel,j."vchFileName",NULL "vchpartycity", NULL "vchpartystate",NULL "vchpartypostalcode", trim(max(k."vchAgentFirstName"))||' '||trim(max(k."vchAgentMiddleName"))||' '||trim(max(k."vchAgentLastName")) as "AgentName" FROM TABLE3 as j join TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID" where j."bFetch" = false GROUP BY j."vchFileName","vchContractEntityRole" , "vchContractEntityRole",j."vchContractNumber","vchContractEntityPersonalIdentifier";

Re: *Regarding brin_index on required column of the table

От

Alban Hertroys

Дата:

21 сентября 2018 г., 20:31:39

Your plan is not readable to me (perhaps because of gmail). Does
https://explain.depesz.com/ give you any useful insights?
On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
>
>
> On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>>
>> Hi
>>
>> Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB  temporarily as total
ramis 16gb 
>>
>> Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on
requiredcolumns 
>>
>>
>>
>> Below is the query plan for explain analyze query :
>>
>> | HashAggregate  (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802
rows=26098loops=1)











          | 
>> |   Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)),
(max((v."vchSequenceNumber")::text)),v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole",
(max((v."vchPartyNatural_Non_NaturalEntity")::text)),(max((v."vchPartyLastName")::text)),
(max((v."vchPartyFirstName")::text)),(max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)),
(max((v."vchPartySuffix")::text)),(NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)),
(max((v."vchTrustRevocabilityIndicator")::text)),(NULL::text), (NULL::text), (max((v."vchFiller1")::text)),
(max((v."vchRejectCode")::text)),(max((v."vchContractEntityAddressLine1")::text)),
(max((v."vchContractEntityAddressLine2")::text)),(max((v."vchContractEntityCity")::text)),
(max((v."vchContractEntityState")::text)),(max((v."vchContractEntityZip")::text)),
(max((v."vchContractEntityAddressLine3")::text)),(max((v."vchContractEntityAddressLine4")::text)),
(max((v."vchContractEntityAddressLine5")::text)),(max((v."vchPartyDateofBirth")::text)),
(max((v."vchPartyAddressLine1")::text)),(max((v."vchContractStatus")::text)), (string_agg(DISTINCT
btrim((s."vchAgentTaxID")::text),','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName",
(max((v.vchpartycity)::text)),(max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT
((((btrim((s."vchAgentFirstName")::text)|| ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) ||
btrim((s."vchAgentLastName")::text)),','::text)) | 
>> |   ->  Append  (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098
loops=1)











      | 
>> |         ->  Unique  (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637
loops=1)











       | 
>> |               ->  Sort  (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637
loops=1)











   | 
>> |                     Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)),
(max((v."vchSequenceNumber")::text)),v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole",
(max((v."vchPartyNatural_Non_NaturalEntity")::text)),(max((v."vchPartyLastName")::text)),
(max((v."vchPartyFirstName")::text)),(max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)),
(max((v."vchPartySuffix")::text)),v."vchPartyID", (max((v."vchPartyIDQualifier")::text)),
(max((v."vchTrustRevocabilityIndicator")::text)),(max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)),
(max((v."vchContractEntityAddressLine1")::text)),(max((v."vchContractEntityAddressLine2")::text)),
(max((v."vchContractEntityCity")::text)),(max((v."vchContractEntityState")::text)),
(max((v."vchContractEntityZip")::text)),(max((v."vchContractEntityAddressLine3")::text)),
(max((v."vchContractEntityAddressLine4")::text)),(max((v."vchContractEntityAddressLine5")::text)),
(max((v."vchPartyDateofBirth")::text)),(max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)),
(string_agg(DISTINCTbtrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName",
(max((v.vchpartycity)::text)),(max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT
((((btrim((s."vchAgentFirstName")::text)|| ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) ||
btrim((s."vchAgentLastName")::text)),','::text))                                            | 
>> |                     Sort Method: quicksort  Memory: 3366kB












 | 
>> |                     ->  GroupAggregate  (cost=26241.74..48849.55 rows=1764 width=1145) (actual
time=1451.987..3297.428rows=3637 loops=1)











                     | 
>> |                           Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID",
v."vchContractPartyRoleQualifier",v."vchContractNumber"











                            | 
>> |                           ->  Sort  (cost=26241.74..26754.95 rows=205285 width=709) (actual
time=1451.641..1474.286rows=232227 loops=1)











                        | 
>> |                                 Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID",
v."vchContractPartyRoleQualifier",v."vchContractNumber"











                       | 
>> |                                 Sort Method: quicksort  Memory: 241964kB












 | 
>> |                                 ->  Merge Join  (cost=23.03..8128.14 rows=205285 width=709) (actual
time=0.072..220.689rows=232227 loops=1)











                | 
>> |                                       Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text)












 | 
>> |                                       ->  Index Scan using cpr_idx1 on "table1" v  (cost=0.28..221.46 rows=4200
width=602)(actual time=0.030..3.283 rows=4200 loops=1)










                                                                                                 | 
>> |                                       ->  Materialize  (cost=0.42..5130.71 rows=125522 width=138) (actual
time=0.007..116.095rows=249620 loops=1)











          | 
>> |                                             ->  Index Scan using cpr_idx2 on "table2" s  (cost=0.42..4816.91
rows=125522width=138) (actual time=0.005..81.432 rows=125522 loops=1)










                                                                                                    | 
>> |         ->  Subquery Scan on "*SELECT* 2"  (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual
time=1803251.051..1803276.051rows=22461 loops=1)











            | 
>> |               ->  Unique  (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual
time=1803251.046..1803266.512rows=22461 loops=1)











                             | 
>> |                     ->  Sort  (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual
time=1803251.043..1803253.167rows=22461 loops=1)











                         | 
>> |                           Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)),
(max((j."vchSequenceNumber")::text)),j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)),
j."vchContractEntityRole",(max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)),
(max((j."vchContractEntityLastName")::text)),(max((j."vchContractEntityFirstName")::text)),
(max((j."vchContractEntityMiddleName")::text)),(max((j."vchContractEntityPrefix")::text)),
(max((j."vchContractEntitySuffix")::text)),(max((j."vchContractEntityE_mailAddress")::text)),
j."vchContractEntityPersonalIdentifier",(max((j."vchContractEntityPersonalQualifier")::text)),
(max((j."vchTrustRevocabilityIndicator")::text)),(max((j."vchContractEntityPhoneNumber")::text)),
(max((j."vchContractEntityPhoneExtension")::text)),(max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)),
(max((j.vchcontractentityaddressline1)::text)),(max((j.vchcontractentityaddressline2)::text)),
(max((j.vchcontractentitycity)::text)),(max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)),
(max((j.vchcontractentityaddressline3)::text)),(max((j.vchcontractentityaddressline4)::text)),
(max((j.vchcontractentityaddressline5)::text)),(string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)),
(max((j."vchAdvisorLabel")::text)),j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) ||
btrim(max((k."vchAgentMiddleName")::text)))|| ' '::text) || btrim(max((k."vchAgentLastName")::text))))
| 
>> |                           Sort Method: quicksort  Memory: 23482kB












 | 
>> |                           ->  GroupAggregate  (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual
time=847482.207..1802617.045rows=22461 loops=1)











         | 
>> |                                 Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber",
j."vchContractEntityPersonalIdentifier"











      | 
>> |                                 ->  Sort  (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual
time=847464.789..1486679.680rows=61595746 loops=1)











          | 
>> |                                       Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber",
j."vchContractEntityPersonalIdentifier"











 | 
>> |                                       Sort Method: external merge  Disk: 42758304kB












 | 
>> |                                       ->  Nested Loop  (cost=0.42..266305.78 rows=59959206 width=677) (actual
time=0.122..73786.837rows=61595746 loops=1)











      | 
>> |                                             ->  Seq Scan on "table3" j  (cost=0.00..669.12 rows=25132 width=591)
(actualtime=0.021..28.338 rows=25132 loops=1)










                                                                                               | 
>> |                                                   Filter: (NOT "bFetch")












 | 
>> |                                             ->  Index Scan using cpr_idx4 on table2 k  (cost=0.42..6.92 rows=365
width=107)(actual time=0.838..2.244 rows=2451 loops=25132)










                                                                                              | 
>> |                                                   Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text)












 | 
>> | Planning time: 2.369 ms












 | 
>> | Execution time: 1807771.091 ms
>>
>>
>>  So i need to execute below query at less time. please help in
>>  optimising the complex query execution time
>>
>>
>> Regards
>>
>> Durgamahesh Manne
>
>
> So i need to execute below query at less time as i just sent query plan to mailing list
>  please help in optimising the complex query execution time
>
> SELECT distinct Max(v."vchSubmittersCode") as vchSubmittersCode   , Max(v."vchRecordType") as  vchRecordType ,
Max(v."vchSequenceNumber")as vchSequenceNumber  , v."vchContractNumber" ,"vchContractPartyRoleQualifier"
,"vchPartyRole",Max("vchPartyNatural_Non_NaturalEntity") as vchPartyNatural_Non_NaturalEntity , Max("vchPartyLastName")
asvchPartyLastName ,Max("vchPartyFirstName") as vchPartyFirstName,Max("vchPartyMiddleName") as vchPartyMiddleName,
Max("vchPartyPrefix")as  vchPartyPrefix ,Max("vchPartySuffix") as vchPartySuffix, NULL
"vchContractEntityE_mailAddress","vchPartyID" , Max("vchPartyIDQualifier") as vchPartyIDQualifier
,Max("vchTrustRevocabilityIndicator")as  vchTrustRevocabilityIndicator,NULL  "vchContractEntityPhoneNumber",NULL
"vchContractEntityPhoneExtension",Max(v."vchFiller1")as vchFiller1,Max(v."vchRejectCode") as vchRejectCode,
Max("vchContractEntityAddressLine1")as vchContractEntityAddressLine1,   Max("vchContractEntityAddressLine2") as
vchContractEntityAddressLine2,Max("vchContractEntityCity") as vchContractEntityCity,  Max("vchContractEntityState") as
vchContractEntityState, Max("vchContractEntityZip") as vchContractEntityZip,  Max("vchContractEntityAddressLine3") as
vchContractEntityAddressLine3,Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4,
Max("vchContractEntityAddressLine5")as vchContractEntityAddressLine5 ,Max("vchPartyDateofBirth") as
vchPartyDateofBirth, Max("vchPartyAddressLine1") as vchPartyAddressLine1,  Max("vchContractStatus") as
vchContractStatus,string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID , "vchPartyRole"
,Max(v."vchAdvisorLabel")as vchAdvisorLabel,v."vchFileName",Max("vchpartycity") as vchpartycity,Max("vchpartystate") as
vchpartystate,Max("vchpartypostalcode")as vchpartypostalcode ,string_agg(distinct trim(s."vchAgentFirstName")||'
'||trim(s."vchAgentMiddleName")||''||trim(s."vchAgentLastName"),',') as "AgentName" FROM TABLE1 as v join"DTCC".TABLE2
ASs on v."vchContractNumber" = s."vchContractNumber" where v."bFetch" = false  GROUP BY
"vchPartyRole",v."vchFileName","vchPartyID","vchPartyRole" ,"vchContractPartyRoleQualifier" , v."vchContractNumber"
UNIONSELECT distinct  max(j."vchSubmittersCode")  as  vchSubmittersCode,max(j."vchRecordType")  as
vchRecordType,max(j."vchSequenceNumber")as vchSequenceNumber  , j."vchContractNumber" ,
max("vchContractEntityTypeCode")as vchContractEntityTypeCode,"vchContractEntityRole"
,max("vchContractEntityNatural_Non_NaturalNameIndicator")as vchContractEntityNatural_Non_NaturalNameIndicator
,max("vchContractEntityLastName")as vchContractEntityLastName ,  max("vchContractEntityFirstName") as
vchContractEntityFirstName, max("vchContractEntityMiddleName") as vchContractEntityMiddleName ,
max("vchContractEntityPrefix")as vchContractEntityPrefix ,  max("vchContractEntitySuffix") as vchContractEntitySuffix,
max("vchContractEntityE_mailAddress") as vchContractEntityE_mailAddress, "vchContractEntityPersonalIdentifier" ,
max("vchContractEntityPersonalQualifier")as vchContractEntityPersonalQualifier, max("vchTrustRevocabilityIndicator") as
vchTrustRevocabilityIndicator, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber,
max("vchContractEntityPhoneExtension")as vchContractEntityPhoneExtension,  max(j."vchFiller1") as vchFiller1,
max(j."vchRejectCode")as vchRejectCode, max("vchcontractentityaddressline1") as vchcontractentityaddressline1
,max("vchcontractentityaddressline2")as  vchcontractentityaddressline2,max("vchcontractentitycity") as
vchcontractentitycity,max("vchcontractentitystate") as vchcontractentitystate,max("vchcontractentityzip") as
vchcontractentityzip,max("vchcontractentityaddressline3") as
vchcontractentityaddressline3,max("vchcontractentityaddressline4")as
vchcontractentityaddressline4,max("vchcontractentityaddressline5")as vchcontractentityaddressline5, NULL
"vchPartyDateofBirth", NULL "vchPartyAddressLine1",  NULL "vchContractStatus", string_agg(distinct
trim(j."vchagenttaxid"),',')as vchagenttaxid, "vchContractEntityRole" , max(j."vchAdvisorLabel") as
vchAdvisorLabel,j."vchFileName",NULL"vchpartycity", NULL "vchpartystate",NULL "vchpartypostalcode",
trim(max(k."vchAgentFirstName"))||''||trim(max(k."vchAgentMiddleName"))||' '||trim(max(k."vchAgentLastName")) as
"AgentName"FROM TABLE3 as j join  TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID"  where j."bFetch" = false
GROUPBY j."vchFileName","vchContractEntityRole" ,
"vchContractEntityRole",j."vchContractNumber","vchContractEntityPersonalIdentifier";
>



--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

21 сентября 2018 г., 21:10:42

Failed to parse the query plan when i submitted query at https://explain.depesz.com/

below is the query plan for the complex query as So i need to execute below query at less time

HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |

>> | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |

>> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |

>> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |

>> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |

>> | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |

>> | Sort Method: quicksort Memory: 3366kB |

>> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |

>> | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |

>> | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |

>> | Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |

>> | Sort Method: quicksort Memory: 241964kB |

>> | -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) |

>> | Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) |

>> | -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual time=0.030..3.283 rows=4200 loops=1) |

>> | -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) |

>> | -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual time=0.005..81.432 rows=125522 loops=1) |

>> | -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 loops=1) |

>> | -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) |

>> | -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) |

>> | Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole", (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)), (max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)), (max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)), (max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier", (max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)), (max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)), (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)), (max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)), (max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)), (max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)), j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k."vchAgentMiddleName")::text))) || ' '::text) || btrim(max((k."vchAgentLastName")::text)))) |

>> | Sort Method: quicksort Memory: 23482kB |

>> | -> GroupAggregate (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual time=847482.207..1802617.045 rows=22461 loops=1) |

>> | Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |

>> | -> Sort (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual time=847464.789..1486679.680 rows=61595746 loops=1) |

>> | Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |

>> | Sort Method: external merge Disk: 42758304kB |

>> | -> Nested Loop (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837 rows=61595746 loops=1) |

>> | -> Seq Scan on "table3" j (cost=0.00..669.12 rows=25132 width=591) (actual time=0.021..28.338 rows=25132 loops=1) |

>> | Filter: (NOT "bFetch") |

>> | -> Index Scan using cpr_idx4 on table2 k (cost=0.42..6.92 rows=365 width=107) (actual time=0.838..2.244 rows=2451 loops=25132) |

>> | Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) |

>> | Planning time: 2.369 ms |

>> | Execution time: 1807771.091 ms

On Fri, Sep 21, 2018 at 8:01 PM Alban Hertroys <haramrae@gmail.com> wrote:

Your plan is not readable to me (perhaps because of gmail). Does
https://explain.depesz.com/ give you any useful insights?
On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
>
>
> On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>>
>> Hi
>>
>> Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gb
>>
>> Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columns
>>
>>
>>
>> Below is the query plan for explain analyze query :
>>
>> | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |
>> | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |
>> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |
>> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |
>> | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | Sort Method: quicksort Memory: 3366kB |
>> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |
>> | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |
>> | Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | Sort Method: quicksort Memory: 241964kB |
>> | -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) |
>> | Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) |
>> | -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual time=0.030..3.283 rows=4200 loops=1) |
>> | -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) |
>> | -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual time=0.005..81.432 rows=125522 loops=1) |
>> | -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 loops=1) |
>> | -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) |
>> | -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) |
>> | Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole", (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)), (max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)), (max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)), (max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier", (max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)), (max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)), (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)), (max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)), (max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)), (max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)), j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k.

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

21 сентября 2018 г., 21:13:21

query is below

On Fri, Sep 21, 2018 at 8:40 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:

Failed to parse the query plan when i submitted query at https://explain.depesz.com/

below is the query plan for the complex query as So i need to execute below query at less time

HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |
>> | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |
>> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |
>> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |
>> | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | Sort Method: quicksort Memory: 3366kB |
>> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |
>> | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |
>> | Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | Sort Method: quicksort Memory: 241964kB |
>> | -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689 rows=232227 loops=1) |
>> | Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) |
>> | -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602) (actual time=0.030..3.283 rows=4200 loops=1) |
>> | -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095 rows=249620 loops=1) |
>> | -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522 width=138) (actual time=0.005..81.432 rows=125522 loops=1) |
>> | -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051 rows=22461 loops=1) |
>> | -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512 rows=22461 loops=1) |
>> | -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167 rows=22461 loops=1) |
>> | Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)), j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole", (max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)), (max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)), (max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)), (max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier", (max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)), (max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)), (max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)), (max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)), (max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)), (max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)), (string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)), j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k."vchAgentMiddleName")::text))) || ' '::text) || btrim(max((k."vchAgentLastName")::text)))) |
>> | Sort Method: quicksort Memory: 23482kB |
>> | -> GroupAggregate (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual time=847482.207..1802617.045 rows=22461 loops=1) |
>> | Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |
>> | -> Sort (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual time=847464.789..1486679.680 rows=61595746 loops=1) |
>> | Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" |
>> | Sort Method: external merge Disk: 42758304kB |
>> | -> Nested Loop (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837 rows=61595746 loops=1) |
>> | -> Seq Scan on "table3" j (cost=0.00..669.12 rows=25132 width=591) (actual time=0.021..28.338 rows=25132 loops=1) |
>> | Filter: (NOT "bFetch") |
>> | -> Index Scan using cpr_idx4 on table2 k (cost=0.42..6.92 rows=365 width=107) (actual time=0.838..2.244 rows=2451 loops=25132) |
>> | Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) |
>> | Planning time: 2.369 ms |
>> | Execution time: 1807771.091 ms

On Fri, Sep 21, 2018 at 8:01 PM Alban Hertroys <haramrae@gmail.com> wrote:
Your plan is not readable to me (perhaps because of gmail). Does
https://explain.depesz.com/ give you any useful insights?
On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
>
>
> On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>>
>> Hi
>>
>> Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gb
>>
>> Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columns
>>
>>
>>
>> Below is the query plan for explain analyze query :
>>
>> | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098 loops=1) |
>> | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), (NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) |
>> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) |
>> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) |
>> | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)), v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)), (max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)), (max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)), (max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text) || ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)), ','::text)) |
>> | Sort Method: quicksort Memory: 3366kB |
>> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428 rows=3637 loops=1) |
>> | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286 rows=232227 loops=1) |
>> | Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier", v."vchContractNumber" |
>> | Sort Method: quicksort Memory: 241964kB

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

21 сентября 2018 г., 21:41:57


Am 21.09.2018 um 17:13 schrieb Durgamahesh Manne:
> query is below

query and plan still not readable. Store it into a textfile and attach 
it here.


Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

21 сентября 2018 г., 21:49:53

On Fri, Sep 21, 2018 at 9:12 PM Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Am 21.09.2018 um 17:13 schrieb Durgamahesh Manne:
> query is below

query and plan still not readable. Store it into a textfile and attach
it here.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Please find below attached query plan file

Regards

Durgamahesh Manne

Вложения

Query plan

Re: *Regarding brin_index on required column of the table

От

Andreas Kretschmer

Дата:

21 сентября 2018 г., 22:32:06


Am 21.09.2018 um 17:49 schrieb Durgamahesh Manne:
> Please find below attached query plan file
>

query and plan still hard to read :-(

Query:

SELECT distinct
   Max(v."vchSubmittersCode") as vchSubmittersCode
   , Max(v."vchRecordType") as  vchRecordType
   , Max(v."vchSequenceNumber") as vchSequenceNumber
   , v."vchContractNumber"
   ,"vchContractPartyRoleQualifier"
   ,"vchPartyRole"
   ,Max("vchPartyNatural_Non_NaturalEntity") as 
vchPartyNatural_Non_NaturalEntity
   , Max("vchPartyLastName") as vchPartyLastName
   ,Max("vchPartyFirstName") as vchPartyFirstName
   ,Max("vchPartyMiddleName") as vchPartyMiddleName
   , Max("vchPartyPrefix") as  vchPartyPrefix
   ,Max("vchPartySuffix") as vchPartySuffix
   , NULL "vchContractEntityE_mailAddress"
   , "vchPartyID"
   , Max("vchPartyIDQualifier") as vchPartyIDQualifier
   ,Max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
   ,NULL  "vchContractEntityPhoneNumber"
   ,NULL "vchContractEntityPhoneExtension"
   ,Max(v."vchFiller1") as vchFiller1
   ,Max(v."vchRejectCode") as vchRejectCode
   , Max("vchContractEntityAddressLine1") as vchContractEntityAddressLine1
   ,   Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2
   , Max("vchContractEntityCity") as vchContractEntityCity
   ,  Max("vchContractEntityState") as vchContractEntityState
   ,  Max("vchContractEntityZip") as vchContractEntityZip
   ,  Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3
   , Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4
   , Max("vchContractEntityAddressLine5") as vchContractEntityAddressLine5
   ,Max("vchPartyDateofBirth") as vchPartyDateofBirth
   ,  Max("vchPartyAddressLine1") as vchPartyAddressLine1
   ,  Max("vchContractStatus") as vchContractStatus
   , string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID
   , "vchPartyRole"
   ,Max(v."vchAdvisorLabel") as vchAdvisorLabel
   ,v."vchFileName"
   ,Max("vchpartycity") as vchpartycity
   ,Max("vchpartystate") as vchpartystate
   ,Max("vchpartypostalcode") as vchpartypostalcode
   ,string_agg(distinct trim(s."vchAgentFirstName")||' 
'||trim(s."vchAgentMiddleName")||' '||trim(s."vchAgentLastName"),',') as 
"AgentName"
FROM
   TABLE1 as v
   join"DTCC".TABLE2 AS s on v."vchContractNumber" = s."vchContractNumber"
where v."bFetch" = false
GROUP BY
   "vchPartyRole"
   ,v."vchFileName"
   ,"vchPartyID"
   ,"vchPartyRole"
   ,"vchContractPartyRoleQualifier"
   , v."vchContractNumber"

UNION SELECT distinct
   max(j."vchSubmittersCode")  as  vchSubmittersCode
   ,max(j."vchRecordType")  as  vchRecordType
   ,max(j."vchSequenceNumber") as vchSequenceNumber
   , j."vchContractNumber"
   , max("vchContractEntityTypeCode") as vchContractEntityTypeCode
   ,"vchContractEntityRole"
   ,max("vchContractEntityNatural_Non_NaturalNameIndicator") as 
vchContractEntityNatural_Non_NaturalNameIndicator
   ,max("vchContractEntityLastName") as vchContractEntityLastName
   ,  max("vchContractEntityFirstName") as vchContractEntityFirstName
   , max("vchContractEntityMiddleName") as vchContractEntityMiddleName
   , max("vchContractEntityPrefix") as vchContractEntityPrefix
   ,  max("vchContractEntitySuffix") as vchContractEntitySuffix
   ,   max("vchContractEntityE_mailAddress") as 
vchContractEntityE_mailAddress
   , "vchContractEntityPersonalIdentifier"
   ,  max("vchContractEntityPersonalQualifier") as 
vchContractEntityPersonalQualifier
   , max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
   ,  max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber
   , max("vchContractEntityPhoneExtension") as 
vchContractEntityPhoneExtension
   ,  max(j."vchFiller1") as vchFiller1
   , max(j."vchRejectCode") as vchRejectCode
   , max("vchcontractentityaddressline1") as vchcontractentityaddressline1
   ,max("vchcontractentityaddressline2") as vchcontractentityaddressline2
   ,max("vchcontractentitycity") as vchcontractentitycity
   , max("vchcontractentitystate") as vchcontractentitystate
   ,max("vchcontractentityzip") as vchcontractentityzip
   , max("vchcontractentityaddressline3") as vchcontractentityaddressline3
   ,max("vchcontractentityaddressline4") as vchcontractentityaddressline4
   ,max("vchcontractentityaddressline5") as vchcontractentityaddressline5
   , NULL "vchPartyDateofBirth"
   ,  NULL "vchPartyAddressLine1"
   ,  NULL "vchContractStatus"
   , string_agg(distinct trim(j."vchagenttaxid"),',') as vchagenttaxid
   , "vchContractEntityRole"
   , max(j."vchAdvisorLabel") as vchAdvisorLabel
   ,j."vchFileName"
   ,NULL "vchpartycity"
   , NULL "vchpartystate"
   ,NULL "vchpartypostalcode"
   ,  trim(max(k."vchAgentFirstName"))||' 
'||trim(max(k."vchAgentMiddleName"))||' 
'||trim(max(k."vchAgentLastName")) as "AgentName"
FROM TABLE3 as j
   join  TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID"
   where j."bFetch" = false
GROUP BY
   j."vchFileName"
   ,"vchContractEntityRole"
   , "vchContractEntityRole"
   ,j."vchContractNumber"
   ,"vchContractEntityPersonalIdentifier"

;


i can see a lot of max(string-field) (for instance, LastName, 
MiddleName, FirstName).
wild guess: completely broken design, but i don't know your application 
and use-case for that.
again, as i said already, i think this is a case for an in-deep 
consultation.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: *Regarding brin_index on required column of the table

От

"Ravi Krishna"

Дата:

21 сентября 2018 г., 22:47:28


> i can see a lot of max(string-field) (for instance, LastName, 
> MiddleName, FirstName).
> wild guess: completely broken design, but i don't know your application 
> and use-case for that.
> again, as i said already, i think this is a case for an in-deep 
> consultation.

My thoughts exactly. There is a UNION also.
A mere look at the SQL indicates that it will be a miracle if this runs fast. Tuning such queries
in a mailing list is difficult, the best we can do is to give suggestion.

New behavior with JDBC 42.2.5

От

"Ravi Krishna"

Дата:

21 сентября 2018 г., 23:12:04

We recently upgraded our JDBC driver to 42.2.5 after seeing this
https://www.postgresql.org/about/news/1883/

All of our PG databases mandates SSL connections.  So the first line in pg_hba.conf is

hostnossl all all all reject

We use dbeaver and while setting up connection we check box SSL (require).  
Attempt to connect without that checked would result in the following error
"pg_hba.conf rejected the connection: SSL off"

With the JDBC version listed in the subject, we are finding that we can connect
to the database even without checking SSL.  Is that expected with this version of 
JDBC. Does it automatically make the connection an SSL one?  Otherwise it is hard
to see PG allowing that connection to pass.

thanks

Re: New behavior with JDBC 42.2.5

От

"Ravi Krishna"

Дата:

21 сентября 2018 г., 23:29:59

Just to clarify:

With JDBC 42.1.4 attempt to connect from dbeaver without SSL results in the following error:

"pg_hba.conf rejected the connection: SSL off"

This is what we expect.

With 42.2.5, the connection succeeds without SSL.

Re: *Regarding brin_index on required column of the table

От

Alban Hertroys

Дата:

22 сентября 2018 г., 01:08:29

> On 21 Sep 2018, at 17:49, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>
> <Query>

Considering how hard you try to get rid of duplicates, I'm quite convinced that you're at least short a few join
conditions.Getting rid of duplicates early has the added benefit of having to aggregate fewer rows, which should
drasticallyimprove the performance of this query. 

In addition, your approach of using both distinct and group by raises a red flag that you're fighting symptoms (most
likely:duplicate results) without understanding their causes. I can't think of a single situation where both are
justifiedin the same (sub-)query. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: New behavior with JDBC 42.2.5

От

Adrian Klaver

Дата:

22 сентября 2018 г., 04:27:09

On 9/21/18 10:12 AM, Ravi Krishna wrote:
> We recently upgraded our JDBC driver to 42.2.5 after seeing this
> https://www.postgresql.org/about/news/1883/
> 
> All of our PG databases mandates SSL connections.  So the first line in pg_hba.conf is
> 
> hostnossl all all all reject
> 
> We use dbeaver and while setting up connection we check box SSL (require).
> Attempt to connect without that checked would result in the following error
> "pg_hba.conf rejected the connection: SSL off"
> 
> With the JDBC version listed in the subject, we are finding that we can connect
> to the database even without checking SSL.  Is that expected with this version of
> JDBC. Does it automatically make the connection an SSL one?  Otherwise it is hard
> to see PG allowing that connection to pass.

First are doing a socket connection or a host connection? Socket 
connections ignore sslmode.

Second what happens if you do some version of this?:

psql postgresql://localhost:5432/test?sslmode=require


psql postgresql://localhost:5432/test?sslmode=disable

> 
> thanks
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: New behavior with JDBC 42.2.5

От

"Ravi Krishna"

Дата:

22 сентября 2018 г., 06:46:57

> First are doing a socket connection or a host connection? Socket 
> connections ignore sslmode.

The URL template of JDBC used by dbeaver is jdbc:postgresql://{host}[:{port}]/[{database}]

From the manual

"The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket connection
ischosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is initiated."
 

In our case the host name does not start with a slash.  So I conclude it is using TCP/IP.


> psql postgresql://localhost:5432/test?sslmode=require
> 
> 
> psql postgresql://localhost:5432/test?sslmode=disable

We are using dbeaver, not psql. dbeaver also provides disable option for sslmode.  I set sslmode=disable and it
immediatelyrejected connection with error "SSL Mode off".  
 

So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button, connection
fails with "SSL Mode off". In that version we check the box "Use SSL" and then in the dropdown
for sslmode, we select sslmode=require. Only then the connection passes.

In version 42.2.5 even if we do not check "Use SSL" it still behaves the same as 
"Use SSL" checked and sslmode=require. 

I was not able to import the security bulletin fully, but am I right in concluding
that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set sslmode=disable.

Thanks for your help.

Re: New behavior with JDBC 42.2.5

От

Adrian Klaver

Дата:

22 сентября 2018 г., 08:13:15

On 9/21/18 5:46 PM, Ravi Krishna wrote:
>> First are doing a socket connection or a host connection? Socket
>> connections ignore sslmode.
> 
> The URL template of JDBC used by dbeaver is jdbc:postgresql://{host}[:{port}]/[{database}]
> 
>>From the manual
> 
> "The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket
connectionis chosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is
initiated."
> 
> In our case the host name does not start with a slash.  So I conclude it is using TCP/IP.
> 
> 
>> psql postgresql://localhost:5432/test?sslmode=require
>>
>>
>> psql postgresql://localhost:5432/test?sslmode=disable
> 
> We are using dbeaver, not psql. dbeaver also provides disable option for sslmode.  I set sslmode=disable and it
immediatelyrejected connection with error "SSL Mode off".
 
> 
> So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button, connection
> fails with "SSL Mode off". In that version we check the box "Use SSL" and then in the dropdown
> for sslmode, we select sslmode=require. Only then the connection passes.
> 
> In version 42.2.5 even if we do not check "Use SSL" it still behaves the same as
> "Use SSL" checked and sslmode=require.
> 
> I was not able to import the security bulletin fully, but am I right in concluding
> that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set sslmode=disable.

Looks that way:

https://github.com/pgjdbc/pgjdbc/commit/cdeeaca47dc3bc6f727c79a582c9e4123099526e

Line 181-184

  * Parameter governing the use of SSL. The allowed values are {@code 
disable}, {@code allow},
    * {@code prefer}, {@code require}, {@code verify-ca}, {@code 
verify-full}.
    * If {@code ssl} property is empty or set to {@code true} it implies 
{@code verify-full}.
    * Default mode is "require"

> 
> Thanks for your help.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: *Regarding brin_index on required column of the table

От

Durgamahesh Manne

Дата:

24 сентября 2018 г., 13:09:09

Thank you all very much for this information

On Sat, Sep 22, 2018 at 12:38 AM Alban Hertroys <haramrae@gmail.com> wrote:

> On 21 Sep 2018, at 17:49, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>
> <Query>

Considering how hard you try to get rid of duplicates, I'm quite convinced that you're at least short a few join conditions. Getting rid of duplicates early has the added benefit of having to aggregate fewer rows, which should drastically improve the performance of this query.

In addition, your approach of using both distinct and group by raises a red flag that you're fighting symptoms (most likely: duplicate results) without understanding their causes. I can't think of a single situation where both are justified in the same (sub-)query.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: *Regarding brin_index on required column of the table

Вложения