Thread: *Regarding brin_index on required column of the table
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
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
Hi
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:
HiRespected postgres community membersI 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 storagedFetch boolean false plainso please help in creating of the BRIN index on above column of the table .RegardsDurgamahesh Manne
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
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
Regards
Durgamahesh Manne
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
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
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
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)
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.comhias per your requesti 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)
On Thu, Sep 20, 2018 at 3:41 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
hias per your requesti ran below query without distinctselect 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 executethe above query took around 7 minutes to execute with distinctOn 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.comhias per your requesti ran explain analyze query without distinct+------------------------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+------------------------------------------------------------------------------------------------------------------------------------------------------+| Hash Join (cost=21.06..457723.28 rows=40500405 width=89) (actual time=0.429..6763.942 rows=40500000 loops=1) || Hash Cond: ((rec."vchSubmittersCode")::text = (sub_head."vchSubmittersCode")::text) || -> Seq Scan on table1 rec (cost=0.00..1822.66 rows=100001 width=80) (actual time=0.006..48.610 rows=100000 loops=1) || Filter: (NOT "bFetch") || Rows Removed by Filter: 4706 || -> Hash (cost=16.00..16.00 rows=405 width=11) (actual time=0.404..0.404 rows=405 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 26kB || -> Seq Scan on table2 sub_head (cost=0.00..16.00 rows=405 width=11) (actual time=0.004..0.326 rows=405 loops=1) || Filter: (NOT "bFetch") || Rows Removed by Filter: 375 || Planning time: 0.351 ms || Execution time: 8371.819 ms |+------------------------------------------------------------------------------------------------------------------------------------------------------+(12 rows)
Hi
Query was executed at less time without distinct
As well as query was taking around 7 minutes to complete execution with distinct
select distinct sub_head."vchSubmittersCode" ,rec."vchFileName" , rec."vchCusipFundIDSubFundID" , rec."vchFundUnitPrice" , sub_head."vchValuationDate" ,rec."vchAdvisorLabel" from table1 rec join table2 sub_head on rec."vchSubmittersCode"=sub_head."vchSubmittersCode" where rec."bFetch"=false and sub_head."bFetch"=false ;
I need to execute above distinct query at less time as distinct query was taking more time to execute even i have created indexes on required columns of the tables
Regards
Durgamahesh Manne
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
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
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.comHisdb[HDD]sdc[HDD]sda[HDD]i checked that there are hdd's in linuxRegards
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
Hi
So i need to execute below query at less time. please help in
optimising the complex query execution time
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
On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
HiComplex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ram is 16gbExplain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on required columnsBelow 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 timeRegardsDurgamahesh 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";
Your plan is not readable to me (perhaps because of gmail). Does https://explain.depesz.com/ give you any useful insights? On Fri, 21 Sep 2018 at 16:15, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote: > > > On Fri, Sep 21, 2018 at 7:38 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote: >> >> Hi >> >> Complex query taken around 30 minutes to execute even i have increased work_mem value to 4GB temporarily as total ramis 16gb >> >> Explain analyze query taken around 30 minutes to execute even i have created partial indexes with where condition on requiredcolumns >> >> >> >> Below is the query plan for explain analyze query : >> >> | HashAggregate (cost=16034967.98..16035010.75 rows=4277 width=1710) (actual time=1806653.536..1806680.802 rows=26098loops=1) | >> | Group Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)),v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)),(max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)),(max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)),(NULL::text), v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)),(NULL::text), (NULL::text), (max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)),(max((v."vchContractEntityAddressLine1")::text)), (max((v."vchContractEntityAddressLine2")::text)),(max((v."vchContractEntityCity")::text)), (max((v."vchContractEntityState")::text)),(max((v."vchContractEntityZip")::text)), (max((v."vchContractEntityAddressLine3")::text)),(max((v."vchContractEntityAddressLine4")::text)), (max((v."vchContractEntityAddressLine5")::text)),(max((v."vchPartyDateofBirth")::text)), (max((v."vchPartyAddressLine1")::text)),(max((v."vchContractStatus")::text)), (string_agg(DISTINCT btrim((s."vchAgentTaxID")::text),','::text)), v."vchPartyRole", (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)),(max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text)|| ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)),','::text)) | >> | -> Append (cost=48944.67..16034550.97 rows=4277 width=1710) (actual time=3324.233..1806605.691 rows=26098 loops=1) | >> | -> Unique (cost=48944.67..49103.43 rows=1764 width=1145) (actual time=3324.233..3327.824 rows=3637 loops=1) | >> | -> Sort (cost=48944.67..48949.08 rows=1764 width=1145) (actual time=3324.232..3324.447 rows=3637 loops=1) | >> | Sort Key: (max((v."vchSubmittersCode")::text)), (max((v."vchRecordType")::text)), (max((v."vchSequenceNumber")::text)),v."vchContractNumber", v."vchContractPartyRoleQualifier", v."vchPartyRole", (max((v."vchPartyNatural_Non_NaturalEntity")::text)),(max((v."vchPartyLastName")::text)), (max((v."vchPartyFirstName")::text)),(max((v."vchPartyMiddleName")::text)), (max((v."vchPartyPrefix")::text)), (max((v."vchPartySuffix")::text)),v."vchPartyID", (max((v."vchPartyIDQualifier")::text)), (max((v."vchTrustRevocabilityIndicator")::text)),(max((v."vchFiller1")::text)), (max((v."vchRejectCode")::text)), (max((v."vchContractEntityAddressLine1")::text)),(max((v."vchContractEntityAddressLine2")::text)), (max((v."vchContractEntityCity")::text)),(max((v."vchContractEntityState")::text)), (max((v."vchContractEntityZip")::text)),(max((v."vchContractEntityAddressLine3")::text)), (max((v."vchContractEntityAddressLine4")::text)),(max((v."vchContractEntityAddressLine5")::text)), (max((v."vchPartyDateofBirth")::text)),(max((v."vchPartyAddressLine1")::text)), (max((v."vchContractStatus")::text)), (string_agg(DISTINCTbtrim((s."vchAgentTaxID")::text), ','::text)), (max((v."vchAdvisorLabel")::text)), v."vchFileName", (max((v.vchpartycity)::text)),(max((v.vchpartystate)::text)), (max((v.vchpartypostalcode)::text)), (string_agg(DISTINCT ((((btrim((s."vchAgentFirstName")::text)|| ' '::text) || btrim((s."vchAgentMiddleName")::text)) || ' '::text) || btrim((s."vchAgentLastName")::text)),','::text)) | >> | Sort Method: quicksort Memory: 3366kB | >> | -> GroupAggregate (cost=26241.74..48849.55 rows=1764 width=1145) (actual time=1451.987..3297.428rows=3637 loops=1) | >> | Group Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier",v."vchContractNumber" | >> | -> Sort (cost=26241.74..26754.95 rows=205285 width=709) (actual time=1451.641..1474.286rows=232227 loops=1) | >> | Sort Key: v."vchPartyRole", v."vchFileName", v."vchPartyID", v."vchContractPartyRoleQualifier",v."vchContractNumber" | >> | Sort Method: quicksort Memory: 241964kB | >> | -> Merge Join (cost=23.03..8128.14 rows=205285 width=709) (actual time=0.072..220.689rows=232227 loops=1) | >> | Merge Cond: ((v."vchContractNumber")::text = (s."vchContractNumber")::text) | >> | -> Index Scan using cpr_idx1 on "table1" v (cost=0.28..221.46 rows=4200 width=602)(actual time=0.030..3.283 rows=4200 loops=1) | >> | -> Materialize (cost=0.42..5130.71 rows=125522 width=138) (actual time=0.007..116.095rows=249620 loops=1) | >> | -> Index Scan using cpr_idx2 on "table2" s (cost=0.42..4816.91 rows=125522width=138) (actual time=0.005..81.432 rows=125522 loops=1) | >> | -> Subquery Scan on "*SELECT* 2" (cost=15985197.45..15985429.90 rows=2513 width=1180) (actual time=1803251.051..1803276.051rows=22461 loops=1) | >> | -> Unique (cost=15985197.45..15985404.77 rows=2513 width=1180) (actual time=1803251.046..1803266.512rows=22461 loops=1) | >> | -> Sort (cost=15985197.45..15985203.73 rows=2513 width=1180) (actual time=1803251.043..1803253.167rows=22461 loops=1) | >> | Sort Key: (max((j."vchSubmittersCode")::text)), (max((j."vchRecordType")::text)), (max((j."vchSequenceNumber")::text)),j."vchContractNumber", (max((j."vchContractEntityTypeCode")::text)), j."vchContractEntityRole",(max((j."vchContractEntityNatural_Non_NaturalNameIndicator")::text)), (max((j."vchContractEntityLastName")::text)),(max((j."vchContractEntityFirstName")::text)), (max((j."vchContractEntityMiddleName")::text)),(max((j."vchContractEntityPrefix")::text)), (max((j."vchContractEntitySuffix")::text)),(max((j."vchContractEntityE_mailAddress")::text)), j."vchContractEntityPersonalIdentifier",(max((j."vchContractEntityPersonalQualifier")::text)), (max((j."vchTrustRevocabilityIndicator")::text)),(max((j."vchContractEntityPhoneNumber")::text)), (max((j."vchContractEntityPhoneExtension")::text)),(max((j."vchFiller1")::text)), (max((j."vchRejectCode")::text)), (max((j.vchcontractentityaddressline1)::text)),(max((j.vchcontractentityaddressline2)::text)), (max((j.vchcontractentitycity)::text)),(max((j.vchcontractentitystate)::text)), (max((j.vchcontractentityzip)::text)), (max((j.vchcontractentityaddressline3)::text)),(max((j.vchcontractentityaddressline4)::text)), (max((j.vchcontractentityaddressline5)::text)),(string_agg(DISTINCT btrim((j.vchagenttaxid)::text), ','::text)), (max((j."vchAdvisorLabel")::text)),j."vchFileName", (((((btrim(max((k."vchAgentFirstName")::text)) || ' '::text) || btrim(max((k."vchAgentMiddleName")::text)))|| ' '::text) || btrim(max((k."vchAgentLastName")::text)))) | >> | Sort Method: quicksort Memory: 23482kB | >> | -> GroupAggregate (cost=10588651.59..15985055.52 rows=2513 width=1180) (actual time=847482.207..1802617.045rows=22461 loops=1) | >> | Group Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" | >> | -> Sort (cost=10588651.59..10738549.61 rows=59959206 width=677) (actual time=847464.789..1486679.680rows=61595746 loops=1) | >> | Sort Key: j."vchFileName", j."vchContractEntityRole", j."vchContractNumber", j."vchContractEntityPersonalIdentifier" | >> | Sort Method: external merge Disk: 42758304kB | >> | -> Nested Loop (cost=0.42..266305.78 rows=59959206 width=677) (actual time=0.122..73786.837rows=61595746 loops=1) | >> | -> Seq Scan on "table3" j (cost=0.00..669.12 rows=25132 width=591) (actualtime=0.021..28.338 rows=25132 loops=1) | >> | Filter: (NOT "bFetch") | >> | -> Index Scan using cpr_idx4 on table2 k (cost=0.42..6.92 rows=365 width=107)(actual time=0.838..2.244 rows=2451 loops=25132) | >> | Index Cond: (("vchAgentTaxID")::text = (j.vchagenttaxid)::text) | >> | Planning time: 2.369 ms | >> | Execution time: 1807771.091 ms >> >> >> So i need to execute below query at less time. please help in >> optimising the complex query execution time >> >> >> Regards >> >> Durgamahesh Manne > > > So i need to execute below query at less time as i just sent query plan to mailing list > please help in optimising the complex query execution time > > SELECT distinct Max(v."vchSubmittersCode") as vchSubmittersCode , Max(v."vchRecordType") as vchRecordType , Max(v."vchSequenceNumber")as vchSequenceNumber , v."vchContractNumber" ,"vchContractPartyRoleQualifier" ,"vchPartyRole",Max("vchPartyNatural_Non_NaturalEntity") as vchPartyNatural_Non_NaturalEntity , Max("vchPartyLastName") asvchPartyLastName ,Max("vchPartyFirstName") as vchPartyFirstName,Max("vchPartyMiddleName") as vchPartyMiddleName, Max("vchPartyPrefix")as vchPartyPrefix ,Max("vchPartySuffix") as vchPartySuffix, NULL "vchContractEntityE_mailAddress","vchPartyID" , Max("vchPartyIDQualifier") as vchPartyIDQualifier ,Max("vchTrustRevocabilityIndicator")as vchTrustRevocabilityIndicator,NULL "vchContractEntityPhoneNumber",NULL "vchContractEntityPhoneExtension",Max(v."vchFiller1")as vchFiller1,Max(v."vchRejectCode") as vchRejectCode, Max("vchContractEntityAddressLine1")as vchContractEntityAddressLine1, Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2,Max("vchContractEntityCity") as vchContractEntityCity, Max("vchContractEntityState") as vchContractEntityState, Max("vchContractEntityZip") as vchContractEntityZip, Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3,Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4, Max("vchContractEntityAddressLine5")as vchContractEntityAddressLine5 ,Max("vchPartyDateofBirth") as vchPartyDateofBirth, Max("vchPartyAddressLine1") as vchPartyAddressLine1, Max("vchContractStatus") as vchContractStatus,string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID , "vchPartyRole" ,Max(v."vchAdvisorLabel")as vchAdvisorLabel,v."vchFileName",Max("vchpartycity") as vchpartycity,Max("vchpartystate") as vchpartystate,Max("vchpartypostalcode")as vchpartypostalcode ,string_agg(distinct trim(s."vchAgentFirstName")||' '||trim(s."vchAgentMiddleName")||''||trim(s."vchAgentLastName"),',') as "AgentName" FROM TABLE1 as v join"DTCC".TABLE2 ASs on v."vchContractNumber" = s."vchContractNumber" where v."bFetch" = false GROUP BY "vchPartyRole",v."vchFileName","vchPartyID","vchPartyRole" ,"vchContractPartyRoleQualifier" , v."vchContractNumber" UNIONSELECT distinct max(j."vchSubmittersCode") as vchSubmittersCode,max(j."vchRecordType") as vchRecordType,max(j."vchSequenceNumber")as vchSequenceNumber , j."vchContractNumber" , max("vchContractEntityTypeCode")as vchContractEntityTypeCode,"vchContractEntityRole" ,max("vchContractEntityNatural_Non_NaturalNameIndicator")as vchContractEntityNatural_Non_NaturalNameIndicator ,max("vchContractEntityLastName")as vchContractEntityLastName , max("vchContractEntityFirstName") as vchContractEntityFirstName, max("vchContractEntityMiddleName") as vchContractEntityMiddleName , max("vchContractEntityPrefix")as vchContractEntityPrefix , max("vchContractEntitySuffix") as vchContractEntitySuffix, max("vchContractEntityE_mailAddress") as vchContractEntityE_mailAddress, "vchContractEntityPersonalIdentifier" , max("vchContractEntityPersonalQualifier")as vchContractEntityPersonalQualifier, max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber, max("vchContractEntityPhoneExtension")as vchContractEntityPhoneExtension, max(j."vchFiller1") as vchFiller1, max(j."vchRejectCode")as vchRejectCode, max("vchcontractentityaddressline1") as vchcontractentityaddressline1 ,max("vchcontractentityaddressline2")as vchcontractentityaddressline2,max("vchcontractentitycity") as vchcontractentitycity,max("vchcontractentitystate") as vchcontractentitystate,max("vchcontractentityzip") as vchcontractentityzip,max("vchcontractentityaddressline3") as vchcontractentityaddressline3,max("vchcontractentityaddressline4")as vchcontractentityaddressline4,max("vchcontractentityaddressline5")as vchcontractentityaddressline5, NULL "vchPartyDateofBirth", NULL "vchPartyAddressLine1", NULL "vchContractStatus", string_agg(distinct trim(j."vchagenttaxid"),',')as vchagenttaxid, "vchContractEntityRole" , max(j."vchAdvisorLabel") as vchAdvisorLabel,j."vchFileName",NULL"vchpartycity", NULL "vchpartystate",NULL "vchpartypostalcode", trim(max(k."vchAgentFirstName"))||''||trim(max(k."vchAgentMiddleName"))||' '||trim(max(k."vchAgentLastName")) as "AgentName"FROM TABLE3 as j join TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID" where j."bFetch" = false GROUPBY j."vchFileName","vchContractEntityRole" , "vchContractEntityRole",j."vchContractNumber","vchContractEntityPersonalIdentifier"; > -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
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.
query is below
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";
On Fri, Sep 21, 2018 at 8:40 PM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
Failed to parse the query plan when i submitted query at https://explain.depesz.com/below is the query plan for the complex query as So i need to execute below query at less timeHashAggregate (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 msOn 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
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
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
Hi
Please find below attached query plan file
Regards
Durgamahesh Manne
Attachment
Am 21.09.2018 um 17:49 schrieb Durgamahesh Manne:
> Please find below attached query plan file
>
query and plan still hard to read :-(
Query:
SELECT distinct
Max(v."vchSubmittersCode") as vchSubmittersCode
, Max(v."vchRecordType") as vchRecordType
, Max(v."vchSequenceNumber") as vchSequenceNumber
, v."vchContractNumber"
,"vchContractPartyRoleQualifier"
,"vchPartyRole"
,Max("vchPartyNatural_Non_NaturalEntity") as
vchPartyNatural_Non_NaturalEntity
, Max("vchPartyLastName") as vchPartyLastName
,Max("vchPartyFirstName") as vchPartyFirstName
,Max("vchPartyMiddleName") as vchPartyMiddleName
, Max("vchPartyPrefix") as vchPartyPrefix
,Max("vchPartySuffix") as vchPartySuffix
, NULL "vchContractEntityE_mailAddress"
, "vchPartyID"
, Max("vchPartyIDQualifier") as vchPartyIDQualifier
,Max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
,NULL "vchContractEntityPhoneNumber"
,NULL "vchContractEntityPhoneExtension"
,Max(v."vchFiller1") as vchFiller1
,Max(v."vchRejectCode") as vchRejectCode
, Max("vchContractEntityAddressLine1") as vchContractEntityAddressLine1
, Max("vchContractEntityAddressLine2") as vchContractEntityAddressLine2
, Max("vchContractEntityCity") as vchContractEntityCity
, Max("vchContractEntityState") as vchContractEntityState
, Max("vchContractEntityZip") as vchContractEntityZip
, Max("vchContractEntityAddressLine3") as vchContractEntityAddressLine3
, Max("vchContractEntityAddressLine4") as vchContractEntityAddressLine4
, Max("vchContractEntityAddressLine5") as vchContractEntityAddressLine5
,Max("vchPartyDateofBirth") as vchPartyDateofBirth
, Max("vchPartyAddressLine1") as vchPartyAddressLine1
, Max("vchContractStatus") as vchContractStatus
, string_agg(distinct trim(s."vchAgentTaxID"),',') as vchAgentTaxID
, "vchPartyRole"
,Max(v."vchAdvisorLabel") as vchAdvisorLabel
,v."vchFileName"
,Max("vchpartycity") as vchpartycity
,Max("vchpartystate") as vchpartystate
,Max("vchpartypostalcode") as vchpartypostalcode
,string_agg(distinct trim(s."vchAgentFirstName")||'
'||trim(s."vchAgentMiddleName")||' '||trim(s."vchAgentLastName"),',') as
"AgentName"
FROM
TABLE1 as v
join"DTCC".TABLE2 AS s on v."vchContractNumber" = s."vchContractNumber"
where v."bFetch" = false
GROUP BY
"vchPartyRole"
,v."vchFileName"
,"vchPartyID"
,"vchPartyRole"
,"vchContractPartyRoleQualifier"
, v."vchContractNumber"
UNION SELECT distinct
max(j."vchSubmittersCode") as vchSubmittersCode
,max(j."vchRecordType") as vchRecordType
,max(j."vchSequenceNumber") as vchSequenceNumber
, j."vchContractNumber"
, max("vchContractEntityTypeCode") as vchContractEntityTypeCode
,"vchContractEntityRole"
,max("vchContractEntityNatural_Non_NaturalNameIndicator") as
vchContractEntityNatural_Non_NaturalNameIndicator
,max("vchContractEntityLastName") as vchContractEntityLastName
, max("vchContractEntityFirstName") as vchContractEntityFirstName
, max("vchContractEntityMiddleName") as vchContractEntityMiddleName
, max("vchContractEntityPrefix") as vchContractEntityPrefix
, max("vchContractEntitySuffix") as vchContractEntitySuffix
, max("vchContractEntityE_mailAddress") as
vchContractEntityE_mailAddress
, "vchContractEntityPersonalIdentifier"
, max("vchContractEntityPersonalQualifier") as
vchContractEntityPersonalQualifier
, max("vchTrustRevocabilityIndicator") as vchTrustRevocabilityIndicator
, max("vchContractEntityPhoneNumber") as vchContractEntityPhoneNumber
, max("vchContractEntityPhoneExtension") as
vchContractEntityPhoneExtension
, max(j."vchFiller1") as vchFiller1
, max(j."vchRejectCode") as vchRejectCode
, max("vchcontractentityaddressline1") as vchcontractentityaddressline1
,max("vchcontractentityaddressline2") as vchcontractentityaddressline2
,max("vchcontractentitycity") as vchcontractentitycity
, max("vchcontractentitystate") as vchcontractentitystate
,max("vchcontractentityzip") as vchcontractentityzip
, max("vchcontractentityaddressline3") as vchcontractentityaddressline3
,max("vchcontractentityaddressline4") as vchcontractentityaddressline4
,max("vchcontractentityaddressline5") as vchcontractentityaddressline5
, NULL "vchPartyDateofBirth"
, NULL "vchPartyAddressLine1"
, NULL "vchContractStatus"
, string_agg(distinct trim(j."vchagenttaxid"),',') as vchagenttaxid
, "vchContractEntityRole"
, max(j."vchAdvisorLabel") as vchAdvisorLabel
,j."vchFileName"
,NULL "vchpartycity"
, NULL "vchpartystate"
,NULL "vchpartypostalcode"
, trim(max(k."vchAgentFirstName"))||'
'||trim(max(k."vchAgentMiddleName"))||'
'||trim(max(k."vchAgentLastName")) as "AgentName"
FROM TABLE3 as j
join TABLE2 AS k on j."vchagenttaxid" = k."vchAgentTaxID"
where j."bFetch" = false
GROUP BY
j."vchFileName"
,"vchContractEntityRole"
, "vchContractEntityRole"
,j."vchContractNumber"
,"vchContractEntityPersonalIdentifier"
;
i can see a lot of max(string-field) (for instance, LastName,
MiddleName, FirstName).
wild guess: completely broken design, but i don't know your application
and use-case for that.
again, as i said already, i think this is a case for an in-deep
consultation.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
> i can see a lot of max(string-field) (for instance, LastName, > MiddleName, FirstName). > wild guess: completely broken design, but i don't know your application > and use-case for that. > again, as i said already, i think this is a case for an in-deep > consultation. My thoughts exactly. There is a UNION also. A mere look at the SQL indicates that it will be a miracle if this runs fast. Tuning such queries in a mailing list is difficult, the best we can do is to give suggestion.
We recently upgraded our JDBC driver to 42.2.5 after seeing this https://www.postgresql.org/about/news/1883/ All of our PG databases mandates SSL connections. So the first line in pg_hba.conf is hostnossl all all all reject We use dbeaver and while setting up connection we check box SSL (require). Attempt to connect without that checked would result in the following error "pg_hba.conf rejected the connection: SSL off" With the JDBC version listed in the subject, we are finding that we can connect to the database even without checking SSL. Is that expected with this version of JDBC. Does it automatically make the connection an SSL one? Otherwise it is hard to see PG allowing that connection to pass. thanks
Just to clarify: With JDBC 42.1.4 attempt to connect from dbeaver without SSL results in the following error: "pg_hba.conf rejected the connection: SSL off" This is what we expect. With 42.2.5, the connection succeeds without SSL.
> On 21 Sep 2018, at 17:49, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote: > > <Query> Considering how hard you try to get rid of duplicates, I'm quite convinced that you're at least short a few join conditions.Getting rid of duplicates early has the added benefit of having to aggregate fewer rows, which should drasticallyimprove the performance of this query. In addition, your approach of using both distinct and group by raises a red flag that you're fighting symptoms (most likely:duplicate results) without understanding their causes. I can't think of a single situation where both are justifiedin the same (sub-)query. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
On 9/21/18 10:12 AM, Ravi Krishna wrote: > We recently upgraded our JDBC driver to 42.2.5 after seeing this > https://www.postgresql.org/about/news/1883/ > > All of our PG databases mandates SSL connections. So the first line in pg_hba.conf is > > hostnossl all all all reject > > We use dbeaver and while setting up connection we check box SSL (require). > Attempt to connect without that checked would result in the following error > "pg_hba.conf rejected the connection: SSL off" > > With the JDBC version listed in the subject, we are finding that we can connect > to the database even without checking SSL. Is that expected with this version of > JDBC. Does it automatically make the connection an SSL one? Otherwise it is hard > to see PG allowing that connection to pass. First are doing a socket connection or a host connection? Socket connections ignore sslmode. Second what happens if you do some version of this?: psql postgresql://localhost:5432/test?sslmode=require psql postgresql://localhost:5432/test?sslmode=disable > > thanks > -- Adrian Klaver adrian.klaver@aklaver.com
> First are doing a socket connection or a host connection? Socket
> connections ignore sslmode.
The URL template of JDBC used by dbeaver is jdbc:postgresql://{host}[:{port}]/[{database}]
From the manual
"The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket connection
ischosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is initiated."
In our case the host name does not start with a slash. So I conclude it is using TCP/IP.
> psql postgresql://localhost:5432/test?sslmode=require
>
>
> psql postgresql://localhost:5432/test?sslmode=disable
We are using dbeaver, not psql. dbeaver also provides disable option for sslmode. I set sslmode=disable and it
immediatelyrejected connection with error "SSL Mode off".
So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button, connection
fails with "SSL Mode off". In that version we check the box "Use SSL" and then in the dropdown
for sslmode, we select sslmode=require. Only then the connection passes.
In version 42.2.5 even if we do not check "Use SSL" it still behaves the same as
"Use SSL" checked and sslmode=require.
I was not able to import the security bulletin fully, but am I right in concluding
that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set sslmode=disable.
Thanks for your help.
On 9/21/18 5:46 PM, Ravi Krishna wrote:
>> First are doing a socket connection or a host connection? Socket
>> connections ignore sslmode.
>
> The URL template of JDBC used by dbeaver is jdbc:postgresql://{host}[:{port}]/[{database}]
>
>>From the manual
>
> "The host component is interpreted as described for the parameter host. In particular, a Unix-domain socket
connectionis chosen if the host part is either empty or starts with a slash, otherwise a TCP/IP connection is
initiated."
>
> In our case the host name does not start with a slash. So I conclude it is using TCP/IP.
>
>
>> psql postgresql://localhost:5432/test?sslmode=require
>>
>>
>> psql postgresql://localhost:5432/test?sslmode=disable
>
> We are using dbeaver, not psql. dbeaver also provides disable option for sslmode. I set sslmode=disable and it
immediatelyrejected connection with error "SSL Mode off".
>
> So the puzzle is -> in version 42.1.4 when we do not check "Use SSL " button, connection
> fails with "SSL Mode off". In that version we check the box "Use SSL" and then in the dropdown
> for sslmode, we select sslmode=require. Only then the connection passes.
>
> In version 42.2.5 even if we do not check "Use SSL" it still behaves the same as
> "Use SSL" checked and sslmode=require.
>
> I was not able to import the security bulletin fully, but am I right in concluding
> that JDBC 42.2.5 always turns on sslmode=require unless we explicitly set sslmode=disable.
Looks that way:
https://github.com/pgjdbc/pgjdbc/commit/cdeeaca47dc3bc6f727c79a582c9e4123099526e
Line 181-184
* Parameter governing the use of SSL. The allowed values are {@code
disable}, {@code allow},
* {@code prefer}, {@code require}, {@code verify-ca}, {@code
verify-full}.
* If {@code ssl} property is empty or set to {@code true} it implies
{@code verify-full}.
* Default mode is "require"
>
> Thanks for your help.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Thank you all very much for this information
On Sat, Sep 22, 2018 at 12:38 AM Alban Hertroys <haramrae@gmail.com> wrote:
> On 21 Sep 2018, at 17:49, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
>
> <Query>
Considering how hard you try to get rid of duplicates, I'm quite convinced that you're at least short a few join conditions. Getting rid of duplicates early has the added benefit of having to aggregate fewer rows, which should drastically improve the performance of this query.
In addition, your approach of using both distinct and group by raises a red flag that you're fighting symptoms (most likely: duplicate results) without understanding their causes. I can't think of a single situation where both are justified in the same (sub-)query.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.