Re: server hardware tuning.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux