我正在尝试优化包含 8000 万行以上的表。获得行数结果需要 20 多分钟。我尝试过集群、vacuum full 和 reindex,但性能没有提高。为了改进数据查询和检索,我需要配置或调整什么?我在 Windows 2019 下使用 Postgresql 12。
更新信息:
- 现在总行数约为 9200 万+
- 表列数 = 44
-
Explain query result using 'select count(*) from doc_details': Finalize Aggregate (cost=5554120.84..5554120.85 rows=1 width=8) (actual time=1249204.001..1249210.027 rows=1 loops=1) -> Gather (cost=5554120.63..5554120.83 rows=2 width=8) (actual time=1249203.642..1249210.020 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=5553120.63..5553120.63 rows=1 width=8) (actual time=1249153.615..1249153.616 rows=1 loops=3) -> Parallel Seq Scan on doc_details (cost=0.00..5456055.30 rows=38826130 width=0) (actual time=3.793..1245165.604 rows=31018949 loops=3) Planning Time: 1.290 ms Execution Time: 1249210.115 ms
(我不知道如何以 kb/mb 为单位获取行大小)
机器信息:
- Windows 2019 数据中心
- 32GB内存
- PostgreSQL 12
表信息:
Table "public.doc_details"
Column | Type | Collation | Nullable | Default
-------------------------+--------------------------------+-----------+----------+----------------------------------------------
id | integer | | not null | nextval('doc_details_id_seq'::regclass)
trans_ref_number | character varying(30) | | not null |
outbound_time | timestamp(0) without time zone | | |
lm_tracking | character varying(30) | | not null |
cargo_dealer_tracking | character varying(30) | | not null |
order_sn | character varying(30) | | |
operations_no | character varying(30) | | |
box_no | character varying(30) | | |
box_size | character varying(30) | | |
parcel_weight_kg | numeric(8,3) | | |
parcel_size | character varying(30) | | |
box_weight_kg | numeric(8,3) | | |
box_volume | integer | | |
parcel_volume | integer | | |
transportation | character varying(100) | | |
channel | character varying(30) | | |
service_code | character varying(20) | | |
country | character varying(60) | | |
destination_code | character varying(20) | | |
assignee_name | character varying(100) | | |
assignee_province_state | character varying(30) | | |
assignee_city | character varying(30) | | |
postal_code | character varying(20) | | |
assignee_telephone | character varying(30) | | |
assignee_address | text | | |
shipper_name | character varying(100) | | |
shipper_country | character varying(60) | | |
shipper_province | character varying(30) | | |
shipper_city | character varying(30) | | |
shipper_address | text | | |
shipper_telephone | character varying(30) | | |
package_qty | integer | | |
hs_code | integer | | |
hs_code_manual | integer | | |
reviewed | boolean | | |
created_at | timestamp(0) without time zone | | |
updated_at | timestamp(0) without time zone | | |
invalid | boolean | | |
arrival_id | integer | | |
excel_row_number | integer | | |
is_additional | boolean | | |
arrival_datetime | timestamp(6) without time zone | | |
invoice_date | timestamp without time zone | | |
unit_code | character varying(100) | | |
Indexes:
"doc_details_pkey" PRIMARY KEY, btree (id) CLUSTER
"doc_details_box_no_idx" btree (box_no)
"doc_details_trans_ref_number_idx" btree (trans_ref_number)
Triggers:
trigger_log_awb_box AFTER INSERT ON doc_details FOR EACH ROW EXECUTE FUNCTION log_awb_box()
来自 PostgreSQL 维基:
参考: 慢计数(PostgreSQL Wiki)
正因为如此,没有更快的方法(对于 PostgreSQL)来读取超过 9400 万行。从您的解释计划中可以看出,PostgreSQL 将煞费苦心地逐行读取。
可能的解决方案
通过允许 PostgreSQL 在内存中存储更多数据,增加文件
shared_buffers
中的设置可能有助于稍微缓解性能问题。postgresql.conf
参考: 20.4。资源消耗(PostgreSQL 文档)
在这种规模下,您必须使用技巧来处理计数,其有效性完全取决于您的业务逻辑。
最“简单”的一种是将结果缓存在应用程序层中,并将 20 分钟的计数查询作为后台任务运行,该任务将在完成时更新缓存。此 ofc 意味着计数将始终落后于“真实”一 20 分钟 + 运行之间的冷却时间。
如果确切的数量不重要,那么您可以使用本文中的建议。
另一种方法是依靠您知道项目
INSERT
和DELETE
查询的确切数量这一事实,因此可以在成功提交时增加/减少“当前”值。假设我们有一个计数表:该
type
列是条件计数schema
和name
组合的鉴别键。对于每个INSERT
和DELETE
查询,您还将增加/减少count
相关行中的值。这种方法除了引入维护开销之外还需要查询和数据库更改,但会产生(几乎)精确的计数。缺点只有当应用程序是数据库的唯一使用者并因此可以完全控制数据修改查询时它才能工作。涉及更改外键的迁移(因为它们可以隐式影响计数)将成为一个完整的 PITA,因此您也必须不时运行开头提到的后台任务。