For test I created two tables with 7800 partitions each and joining them sees performance bottleneck. It is taking 5 seconds planning time. Please see attached plan.
Regards,
Virendra
-----Original Message-----
From: pinker [mailto:pinker@xxxxxxx]
Sent: Wednesday, January 10, 2018 12:07 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: How Many Partitions are Good Performing
I've run once a test on my laptop because was curious as well. From my results (on laptop - 16GB RAM, 4 cores) the upper limit was 12k. Above it planning time was unbearable high - much higher than execution time. It's been tested on 9.5
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
________________________________
This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.
Aggregate (cost=337270.14..337270.15 rows=1 width=232) (actual time=102.123..102.124 rows=1 loops=1)
Buffers: shared hit=22747
CTE t1
-> HashAggregate (cost=2773.81..2795.37 rows=2156 width=24) (actual time=12.051..13.626 rows=2792 loops=1)
Group Key: se_1.site_id, se_1.peril_id, se_1.account_id, se_1.portfolio_id
Buffers: shared hit=385
-> Nested Loop (cost=10.05..2752.25 rows=2156 width=24) (actual time=0.359..9.940 rows=2792 loops=1)
Buffers: shared hit=385
-> Seq Scan on catevent_earthquake_poly_merged (cost=0.00..17.75 rows=3 width=32) (actual time=0.027..0.030 rows=1 loops=1)
Filter: (event_id = 'us20009yvq'::text)
Rows Removed by Filter: 3
Buffers: shared hit=2
-> Append (cost=10.05..910.78 rows=72 width=56) (actual time=0.328..7.250 rows=2792 loops=1)
Buffers: shared hit=383
-> Bitmap Heap Scan on site_exposure_13397 se_1 (cost=10.05..910.78 rows=72 width=56) (actual time=0.325..4.726 rows=2792 loops=1)
Recheck Cond: (shape && catevent_earthquake_poly_merged.shape)
Filter: ((portfolio_id = 13397) AND _st_intersects(shape, catevent_earthquake_poly_merged.shape))
Heap Blocks: exact=360
Buffers: shared hit=383
-> Bitmap Index Scan on site_exposure_13397_shape_idx (cost=0.00..10.03 rows=216 width=0) (actual time=0.263..0.263 rows=2792 loops=1)
Index Cond: (shape && catevent_earthquake_poly_merged.shape)
Buffers: shared hit=23
-> Hash Join (cost=271937.09..334470.48 rows=59 width=200) (actual time=88.676..98.896 rows=2792 loops=1)
Hash Cond: ((se.peril_id = p.pe_peril_id) AND (se.account_id = p.pe_account_id))
Buffers: shared hit=22747
-> GroupAggregate (cost=271794.39..330553.67 rows=215630 width=152) (actual time=30.641..37.303 rows=2792 loops=1)
Group Key: se.peril_id, se.portfolio_id, se.account_id
Buffers: shared hit=11178
-> Sort (cost=271794.39..274489.77 rows=1078152 width=148) (actual time=30.629..32.018 rows=2792 loops=1)
Sort Key: se.peril_id, se.account_id
Sort Method: quicksort Memory: 838kB
Buffers: shared hit=11178
-> Nested Loop (cost=48.94..1614.87 rows=1078152 width=148) (actual time=3.305..27.195 rows=2792 loops=1)
Buffers: shared hit=11178
-> HashAggregate (cost=48.51..50.51 rows=200 width=8) (actual time=3.274..4.957 rows=2792 loops=1)
Group Key: t1.site_id
-> CTE Scan on t1 (cost=0.00..43.12 rows=2156 width=8) (actual time=0.002..1.378 rows=2792 loops=1)
-> Append (cost=0.43..7.81 rows=1 width=156) (actual time=0.004..0.006 rows=1 loops=2792)
Buffers: shared hit=11178
-> Index Scan using site_exposure_13397_pkey on site_exposure_13397 se (cost=0.43..7.81 rows=1 width=156) (actual time=0.003..0.004 rows=1 loops=2792)
Index Cond: (site_id = t1.site_id)
Filter: (portfolio_id = 13397)
Buffers: shared hit=11178
-> Hash (cost=142.53..142.53 rows=11 width=80) (actual time=58.016..58.016 rows=2792 loops=1)
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 349kB
Buffers: shared hit=11569
-> Subquery Scan on p (cost=141.98..142.53 rows=11 width=80) (actual time=48.018..56.051 rows=2792 loops=1)
Buffers: shared hit=11569
-> GroupAggregate (cost=141.98..142.42 rows=11 width=80) (actual time=48.016..53.268 rows=2792 loops=1)
Group Key: pe.peril_id, pe.portfolio_id, pe.account_id
Buffers: shared hit=11569
-> Sort (cost=141.98..142.01 rows=11 width=80) (actual time=48.002..49.286 rows=2792 loops=1)
Sort Key: pe.peril_id, pe.account_id
Sort Method: quicksort Memory: 489kB
Buffers: shared hit=11569
-> Nested Loop (cost=49.02..141.79 rows=11 width=80) (actual time=18.957..45.059 rows=2792 loops=1)
Buffers: shared hit=11569
-> HashAggregate (cost=48.59..48.70 rows=11 width=16) (actual time=18.922..20.890 rows=2792 loops=1)
Group Key: t2.peril_id, t2.account_id, t2.portfolio_id
Buffers: shared hit=385
-> CTE Scan on t1 t2 (cost=0.00..48.51 rows=11 width=16) (actual time=12.055..16.855 rows=2792 loops=1)
Filter: (portfolio_id = 13397)
Buffers: shared hit=385
-> Append (cost=0.43..8.45 rows=1 width=80) (actual time=0.005..0.007 rows=1 loops=2792)
Buffers: shared hit=11184
-> Index Scan using policy_exposure_13397_portfolio_id_peril_id_account_id_idx on policy_exposure_13397 pe (cost=0.43..8.45 rows=1 width=80) (actual time=0.004..0.005 rows=1 loops=2792)
Index Cond: ((portfolio_id = 13397) AND (peril_id = t2.peril_id) AND (account_id = t2.account_id))
Buffers: shared hit=11184
Planning time: 5225.920 ms
Execution time: 104.319 ms
(70 rows)
Time: 6705.007 ms (00:06.705)
