*Regarding brin_index on required column of the table

Started by Durgamahesh Manneover 7 years ago29 messagesgeneral

maheshpostgres9@gmail.com

over 7 years ago

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

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#1)

Re: *Regarding brin_index on required column of the table

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

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#1)

Re: *Regarding brin_index on required column of the table

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:

Show quoted text

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

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Andreas Kretschmer (#2)

Re: *Regarding brin_index on required column of the table

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

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#4)

Re: *Regarding brin_index on required column of the table

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

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Andreas Kretschmer (#5)

Re: *Regarding brin_index on required column of the table

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

Hi
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

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#6)

Re: *Regarding brin_index on required column of the table

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

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Andreas Kretschmer (#7)

Re: *Regarding brin_index on required column of the table

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)

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#8)

Re: *Regarding brin_index on required column of the table

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:

Show quoted text

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)

#10

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#9)

Re: *Regarding brin_index on required column of the table

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

#11

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#10)

Re: *Regarding brin_index on required column of the table

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

#12

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Andreas Kretschmer (#11)

Re: *Regarding brin_index on required column of the table

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

#13

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#12)

Re: *Regarding brin_index on required column of the table

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

#14

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#1)

Re: *Regarding brin_index on required column of the table

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

Import Notes

Reply to msg id not found: CAJCZkoJr_ro=w8OMq8Ge0vZY8u4tggOG+U35Tisz67Y+qLosvg@mail.gmail.com

#15

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#14)

Re: *Regarding brin_index on required column of the table

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";

#16

Alban Hertroys

haramrae@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#15)

Re: *Regarding brin_index on required column of the table

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."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";

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

#17

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Alban Hertroys (#16)

Re: *Regarding brin_index on required column of the table

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.

#18

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Durgamahesh Manne (#17)

Re: *Regarding brin_index on required column of the table

query is below

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

Show quoted text

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

#19

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#18)

Re: *Regarding brin_index on required column of the table

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

#20

Durgamahesh Manne

maheshpostgres9@gmail.com

over 7 years ago

In reply to: Andreas Kretschmer (#19)

Re: *Regarding brin_index on required column of the table

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

#21

Andreas Kretschmer

andreas@a-kretschmer.de

over 7 years ago

In reply to: Durgamahesh Manne (#20)

#22