Fw: server hardware tuning.

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Fw: server hardware tuning.

suganthi Sekar


Hi ,


I need know how to calculate hardware sizing for database or query


RAM

CPU

Config tuning


Requirement : 


1100 concurrent connection

1600 column of table

1GB of data can be select and dynamic aggregation will happen


Regards

SuganthiSekar

Reply | Threaded
Open this post in threaded view
|

Re: Fw: server hardware tuning.

Prince Pathria
Hi Suganthi,
I can give you a start, some pro users can suggest you better.

1. Don't use this much of connections on a single postgres server. Use a connection pooler in front of it.
2. RAM: Depends upon how much data you want to be cached.
3. Use PCIe SATA SSD with RAID10, Postgres uses a lot of IO for its operations. 
4. For config tuning: https://pgtune.leopard.in.ua/#/ Though please go through all params for more understanding

Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com


On Mon, Feb 4, 2019 at 6:07 PM suganthi Sekar <[hidden email]> wrote:


Hi ,


I need know how to calculate hardware sizing for database or query


RAM

CPU

Config tuning


Requirement : 


1100 concurrent connection

1600 column of table

1GB of data can be select and dynamic aggregation will happen


Regards

SuganthiSekar

Reply | Threaded
Open this post in threaded view
|

Sv: Re: Fw: server hardware tuning.

Andreas Joseph Krogh-2
På mandag 04. februar 2019 kl. 15:45:30, skrev Prince Pathria <[hidden email]>:
Hi Suganthi,
I can give you a start, some pro users can suggest you better.
 
1. Don't use this much of connections on a single postgres server. Use a connection pooler in front of it.
2. RAM: Depends upon how much data you want to be cached.
3. Use PCIe SATA SSD with RAID10, Postgres uses a lot of IO for its operations. 
4. For config tuning: https://pgtune.leopard.in.ua/#/ Though please go through all params for more understanding
 
Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com
 
There's no such thing as PCIe SATA, use PCIe or NVMe in RAID-10, it's quite affordable these days and meaningless not to use.
 
--
Andreas Joseph Krogh
Reply | Threaded
Open this post in threaded view
|

Re: server hardware tuning.

suganthi Sekar
In reply to this post by suganthi Sekar

Hi Team , 

 i am using Postgresql 11, i have 2 partition table , when i joined both table in query 
a table  its goes exact partition table  , but other table scan all partition

please clarify on this .

i have enabled below parameter on in configuration file
Note  :  alter system set enable_partitionwise_join  to 'on';


Example : 

explain analyze
select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
 where a.call_created_date ='2017-11-01' and '2017-11-30'



 "Hash Right Join  (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)"
"  Hash Cond: (b.call_id = a.call_id)"
"  ->  Append  (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)"
"        ->  Seq Scan on call_report2 b  (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)"
"        ->  Seq Scan on call_report2_201803 b_1  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)"
"        ->  Seq Scan on call_report2_201711 b_2  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)"
"        ->  Seq Scan on call_report2_201712 b_3  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)"
"        ->  Seq Scan on call_report2_201801 b_4  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)"
"  ->  Hash  (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 12kB"
"        ->  Append  (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)"
"              ->  Seq Scan on call_report1 a  (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)"
"                    Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
"              ->  Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1  (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)"
"                    Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
"Planning Time: 20.866 ms"
"Execution Time: 1.205 ms"


From: suganthi Sekar
Sent: 04 February 2019 15:27:31
To: [hidden email]
Subject: Fw: server hardware tuning.
 


Hi ,


I need know how to calculate hardware sizing for database or query


RAM

CPU

Config tuning


Requirement : 


1100 concurrent connection

1600 column of table

1GB of data can be select and dynamic aggregation will happen


Regards

SuganthiSekar

Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Justin Pryzby
On Thu, Feb 14, 2019 at 09:38:52AM +0000, suganthi Sekar wrote:

>  i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table  its goes exact partition table  , but other table scan all partition
>
> please clarify on this .
>
> Example :
>
> explain analyze
> select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
>  where a.call_created_date ='2017-11-01' and '2017-11-30'

Looks like this query waas manally editted and should say:
>  where a.call_created_date >='2017-11-01' AND a.call_created_date<'2017-11-30'
Right?

The issue is described well here:
https://www.postgresql.org/message-id/flat/7DF51702-0F6A-4571-80BB-188AAEF260DA%40gmail.com
https://www.postgresql.org/message-id/499.1496696552%40sss.pgh.pa.us

You can work around it by specifying the same condition on b.call_created_date:
>  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

Justin

Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

suganthi Sekar

HI,


u mean the below parameter need to set on . its already on only.


  alter system set  constraint_exclusion  to 'on';


Regards,

Suganthi Sekar


From: Justin Pryzby <[hidden email]>
Sent: 14 February 2019 15:35:33
To: suganthi Sekar
Cc: [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
On Thu, Feb 14, 2019 at 09:38:52AM +0000, suganthi Sekar wrote:
>  i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table  its goes exact partition table  , but other table scan all partition
>
> please clarify on this .
>
> Example :
>
> explain analyze
> select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
>  where a.call_created_date ='2017-11-01' and '2017-11-30'

Looks like this query waas manally editted and should say:
>  where a.call_created_date >='2017-11-01' AND a.call_created_date<'2017-11-30'
Right?

The issue is described well here:
https://www.postgresql.org/message-id/flat/7DF51702-0F6A-4571-80BB-188AAEF260DA%40gmail.com
https://www.postgresql.org/message-id/499.1496696552%40sss.pgh.pa.us

You can work around it by specifying the same condition on b.call_created_date:
>  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

Justin
Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Justin Pryzby
On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

suganthi Sekar
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar

From: Justin Pryzby <[hidden email]>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'
Reply | Threaded
Open this post in threaded view
|

Re: partition pruning

Laurenz Albe
In reply to this post by suganthi Sekar
suganthi Sekar wrote:

>  i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table  its goes exact partition table  , but other table scan all partition
>
> please clarify on this .
>
> i have enabled below parameter on in configuration file
> Note  :  alter system set enable_partitionwise_join  to 'on';
>
>
> Example :
>
> explain analyze
> select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
>  where a.call_created_date ='2017-11-01' and '2017-11-30'
>
>
>
>  "Hash Right Join  (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)"
> "  Hash Cond: (b.call_id = a.call_id)"
> "  ->  Append  (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)"
> "        ->  Seq Scan on call_report2 b  (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)"
> "        ->  Seq Scan on call_report2_201803 b_1  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)"
> "        ->  Seq Scan on call_report2_201711 b_2  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)"
> "        ->  Seq Scan on call_report2_201712 b_3  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)"
> "        ->  Seq Scan on call_report2_201801 b_4  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)"
> "  ->  Hash  (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)"
> "        Buckets: 1024  Batches: 1  Memory Usage: 12kB"
> "        ->  Append  (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)"
> "              ->  Seq Scan on call_report1 a  (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)"
> "                    Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "              ->  Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1  (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)"
> "                    Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "Planning Time: 20.866 ms"
> "Execution Time: 1.205 ms"

There is no condition on the table "call_report2" in your query,
so it is not surprising that all partitions are scanned, right?

You have to add a WHERE condition that filters on the partitioning
column(s) of "call_report2".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


Reply | Threaded
Open this post in threaded view
|

Re: partition pruning

suganthi Sekar

HI ,


Ok thanks.


Regards,

Suganthi Sekar



From: Laurenz Albe <[hidden email]>
Sent: 14 February 2019 18:07:49
To: suganthi Sekar; [hidden email]
Subject: Re: partition pruning
 
suganthi Sekar wrote:
>  i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table  its goes exact partition table  , but other table scan all partition
>
> please clarify on this .
>
> i have enabled below parameter on in configuration file
> Note  :  alter system set enable_partitionwise_join  to 'on';
>
>
> Example :
>
> explain analyze
> select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
>  where a.call_created_date ='2017-11-01' and '2017-11-30'
>
>
>
>  "Hash Right Join  (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)"
> "  Hash Cond: (b.call_id = a.call_id)"
> "  ->  Append  (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)"
> "        ->  Seq Scan on call_report2 b  (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)"
> "        ->  Seq Scan on call_report2_201803 b_1  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)"
> "        ->  Seq Scan on call_report2_201711 b_2  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)"
> "        ->  Seq Scan on call_report2_201712 b_3  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)"
> "        ->  Seq Scan on call_report2_201801 b_4  (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)"
> "  ->  Hash  (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)"
> "        Buckets: 1024  Batches: 1  Memory Usage: 12kB"
> "        ->  Append  (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)"
> "              ->  Seq Scan on call_report1 a  (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)"
> "                    Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "              ->  Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1  (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)"
> "                    Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))"
> "Planning Time: 20.866 ms"
> "Execution Time: 1.205 ms"

There is no condition on the table "call_report2" in your query,
so it is not surprising that all partitions are scanned, right?

You have to add a WHERE condition that filters on the partitioning
column(s) of "call_report2".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Michael Lewis
In reply to this post by suganthi Sekar
What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <[hidden email] wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar

From: Justin Pryzby <[hidden email]>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'
Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

suganthi Sekar
Both table Portion by  same column call_created_date
________________________________
From: Michael Lewis <[hidden email]>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <[hidden email]<mailto:[hidden email]> wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar
________________________________
From: Justin Pryzby <[hidden email]<mailto:[hidden email]>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]<mailto:[hidden email]>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

Reply | Threaded
Open this post in threaded view
|

Re: partition pruning

Justin Pryzby
In reply to this post by Laurenz Albe
On Thu, Feb 14, 2019 at 01:37:49PM +0100, Laurenz Albe wrote:
> There is no condition on the table "call_report2" in your query,
> so it is not surprising that all partitions are scanned, right?

Some people find it surprising, since: a.call_id=b.call_id

suganthi Sekar wrote:
> > explain analyze
> > select * from call_report1 as a  inner join  call_report2 as b on a.call_id=b.call_id
> >  where a.call_created_date ='2017-11-01' and '2017-11-30'

Justin

Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Michael Lewis
In reply to this post by suganthi Sekar
Yeah, the planner doesn't know that call_created_date can be limited on both tables unless you tell it specify it in the where condition as Laurenz said on another thread.


Michael Lewis

On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar <[hidden email]> wrote:
Both table Portion by  same column call_created_date
________________________________
From: Michael Lewis <[hidden email]>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <[hidden email]<mailto:[hidden email]> wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar
________________________________
From: Justin Pryzby <[hidden email]<mailto:[hidden email]>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]<mailto:[hidden email]>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'
Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

suganthi Sekar

Hi,


yes i accept , but when i will do for existing tables, i am facing issue.



I have created 100 Function , all the function having  five table join(now all partition by date) , now its not possible to change where condition in all 100 Function.

so that i am trying any other possibilities are there. 



Regards,

Suganthi Sekar


From: Michael Lewis <[hidden email]>
Sent: 15 February 2019 00:20:00
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
Yeah, the planner doesn't know that call_created_date can be limited on both tables unless you tell it specify it in the where condition as Laurenz said on another thread.


Michael Lewis

On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar <[hidden email]> wrote:
Both table Portion by  same column call_created_date
________________________________
From: Michael Lewis <[hidden email]>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <[hidden email]<mailto:[hidden email]> wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar
________________________________
From: Justin Pryzby <[hidden email]<mailto:[hidden email]>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]<mailto:[hidden email]>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'
Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Benedict Holland
Hi Sugathi, 

That sounds like a perfect task for a view if the joins are all the same.

~Ben



On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar <[hidden email]> wrote:

Hi,


yes i accept , but when i will do for existing tables, i am facing issue.



I have created 100 Function , all the function having  five table join(now all partition by date) , now its not possible to change where condition in all 100 Function.

so that i am trying any other possibilities are there. 



Regards,

Suganthi Sekar


From: Michael Lewis <[hidden email]>
Sent: 15 February 2019 00:20:00
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
Yeah, the planner doesn't know that call_created_date can be limited on both tables unless you tell it specify it in the where condition as Laurenz said on another thread.


Michael Lewis

On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar <[hidden email]> wrote:
Both table Portion by  same column call_created_date
________________________________
From: Michael Lewis <[hidden email]>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; [hidden email]
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <[hidden email]<mailto:[hidden email]> wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar
________________________________
From: Justin Pryzby <[hidden email]<mailto:[hidden email]>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: [hidden email]<mailto:[hidden email]>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'
Reply | Threaded
Open this post in threaded view
|

Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

Justin Pryzby
On Fri, Feb 15, 2019 at 12:24:18PM -0500, Benedict Holland wrote:
> That sounds like a perfect task for a view if the joins are all the same.

But note that either the view itself needs to have both where clauses (with
hardcoded dates?), or otherwise the view needs to be on only one table, and the
toplevel query needs to have where clause on each view, or else one of the
tables won't get constraint exclusion.

On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar <[hidden email]> wrote:
> > yes i accept , but when i will do for existing tables, i am facing issue.
> >
> > I have created 100 Function , all the function having  five table join(*now
> > all partition by date*) , now its not possible to change where condition
> > in all 100 Function.
> >
> > so that i am trying any other possibilities are there.

> > From: Justin Pryzby <[hidden email]<mailto:[hidden email]>>
> > Sent: 14 February 2019 16:10:01
> > To: suganthi Sekar
> > Cc: [hidden email]<mailto:
> > [hidden email]>
> > Subject: Re: constraint exclusion with ineq condition (Re: server hardware
> > tuning.)
> >
> > On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> > > u mean the below parameter need to set on . its already on only.
> > >   alter system set  constraint_exclusion  to 'on';
> >
> > No, I said:
> > > You can work around it by specifying the same condition on
> > b.call_created_date:
> > > >  AND b.call_created_date >='2017-11-01' AND
> > b.call_created_date<'2017-11-30'

--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581