postgresql - postgreSQL 分区之间的简单等值连接需要很长时间（10 分钟）

Question

我们正在使用 postgreSQL 数据库，我遇到了一些限制。我根据公司名称在 company_sale_account 表上有分区

我们生成一份关于两者之间匹配的帐户的报告。以下是查询：

SELECT cpsa1.*
FROM company_sale_account cpsa1  
 JOIN  company_sale_account cpsa2  ON cpsa1.sale_account_id = cpsa2.sale_account_id 
 WHERE  cpsa1.company_name = 'company_a'  
 AND cpsa2.company_name = 'company_b' 

我们在两个表的 sale_account_id 列上都设置了 BTREE 索引。直到最近，这都很好。现在，我们在 company_a 分区中有 1000 万行，在 company_b 分区中有 700 万行。此查询需要 10 多分钟。

下面是它的解释计划输出：

Buffers: shared hit=20125996 read=47811 dirtied=75, temp read=1333427 written=1333427   I/O Timings: read=19619.322 
   Sort  (cost=167950986.43..168904299.23 rows=381325118 width=132) (actual time=517017.334..603691.048 rows=16854094 loops=1) 
       Sort Key: cpsa1.crm_account_id, ((cpsa1.account_name)::text),((cpsa1.account_owner)::text), ((cpsa1.account_type)::text), cpsa1.is_customer, ((date_part('epoch'::text,cpsa1.created_date))::integer),((hstore_to_json(cpsa1.custom_crm_fields))::tex (...) 
        Sort Method: external merge  Disk: 2862656kB 
        Buffers: shared hit=20125996 read=47811 dirtied=75, temp read=1333427 written=1333427
        I/O Timings: read=19619.322 
        - Nested Loop (cost=0.00..9331268.39 rows=381325118 width=132) (actual time=1.680..118698.570 rows=16854094 loops=1) 
              Buffers: shared hit=20125977 read=47811 dirtied=75 
              I/O Timings: read=19619.322 
              - Append  (cost=0.00..100718.94 rows=2033676 width=33) (actual time=0.014..1783.243 rows=2033675 loops=1) 
                    Buffers: shared hit=75298 dirtied=75 
- Seq Scan on company_sale_account cpsa2  (cost=0.00..0.00 rows=1 width=516) (actual time=0.001..0.001 rows=0 loops=1) 
               Filter: ((company_name)::text = 'company_b'::text) 
                 - Seq Scan on company_sale_account_concur cpsa2_1  (cost=0.00..100718.94 rows=2033675 width=33) (actual time=0.013..938.145 rows=2033675 loops=1) 
                         Filter: ((company_name)::text = 'company_b'::text) 
                 Buffers: shared hit=75298 dirtied=75 
              - Append (cost=0.00..1.97 rows=23 width=355) (actual time=0.034..0.047 rows=8 loops=2033675) 
                    Buffers: shared hit=20050679 read=47811 
                    I/O Timings: read=19619.322 
       - Seq Scan on company_sale_account cpsa1  (cost=0.00..0.00 rows=1 width=4525) (actual time=0.000..0.000 rows=0 loops=2033675) 
        Filter: (((company_name)::text = 'company_a'::text) AND ((cpsa2.sale_account_id)::text = (sale_account_id)::text)) 
         - Index Scan using ix_csa_adp_sale_account on company_sale_account_adp cpsa1_1  (cost=0.56..1.97 rows=22 width=165) (actual time=0.033..0.042 rows=8 loops=2033675) 
                    Index Cond: ((sale_account_id)::text = (cpsa2.sale_account_id)::text)
                          Filter: ((company_name)::text = 'company_a'::text) 
                          Buffers: shared hit=20050679 read=47811 
                          I/O Timings: read=19619.322 
Planning time: 30.853 ms 
Execution time: 618218.321ms

您对如何调整 postgres 有任何建议吗？请分享你的想法。这对我会有很大的帮助。

标签： postgresqljoinpartition