问题
我怎样才能使这篇文章中描述的查询更快，特别是通过使 PostgreSQL 使用可用的 RAM？- 请注意，我已尝试适当配置effective_cache_size和shared_buffers。见下文。
背景
我必须定期将约 2.6 亿行的表 (coreg_master) 与新传入的数据连接起来。我已经对表进行了分区，以便每个分区都适合 RAM。当然，我也设置了适当的索引。但是，当将分区表与其他（小得多的）表单独连接时，它会在磁盘上执行完全随机的 IO。这是由于对大表进行了嵌套循环索引扫描，由于我们的磁盘设置不佳，因此速度非常慢。
我希望它使用所有可用的 RAM 来缓存大型分区表，我知道这应该由 Linux 内核/文件系统本身完成。但它仍然没有将表加载到 RAM 中，尽管它可以容纳。我猜这是因为访问模式不是连续的，因此不会触发缓存？我不知道。查询计划和配置参数如下。
表结构
这是我的大型表 coreg_master 的一个分区。分区表名为 coreg_a、coreg_b 等。
\d coreg_a
Table "public.coreg_a"
Column | Type | Modifiers
-------------+-------------------+-----------------------------------------------------------
id | integer | not null default nextval('coreg_master_id_seq'::regclass)
first_name | character varying |
last_name | character varying |
phone | character varying |
city | character varying |
zip | integer |
address | character varying |
dob | date |
ip | character varying |
source | character varying |
gender | character varying |
state | character varying |
record_date | date |
email | character varying |
Indexes:
"coreg_a_name" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
"coreg_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: coreg_master
以下是表 appendable_24 的分区，这是该表与 coreg_master 连接的示例。它的分区方式与 coreg_master 相同，因此实际上 coreg_a 与 appendable_24_a 等逐个连接。
\d appendable_24_a
Table "public.appendable_24_a"
Column | Type | Modifiers
------------+-------------------+-----------
line_num | integer | not null
first_name | character varying |
last_name | character varying |
address | character varying |
state | character varying |
zip | integer |
Indexes:
"appendable_24_a_name_index" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
"appendable_24_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: appendable_24
询问 ＆EXPLAIN ANALYZE
以下是最小连接（表根据 first_name 列的第一个字母进行分区）的 explain analyze 输出，因为它不会花费很长时间。但是，查询计划对于每个分区上的所有连接都是相同的，因此它也应该代表较大的连接（请注意，我处理了ANALYZE表，总时间实际上是 20 秒，但由于结果被缓存了，所以这里更快）：
explain analyze SELECT
coreg_x.phone,
coreg_x.email,
coreg_x.record_date,
appendable_24_x.line_num
FROM appendable_24_x INNER JOIN coreg_x ON
lower(appendable_24_x.first_name) = lower(coreg_x.first_name) AND
lower(appendable_24_x.last_name) = lower(coreg_x.last_name) AND
(coreg_x.phone IS NOT NULL OR coreg_x.email IS NOT NULL) AND
similarity(lower(appendable_24_x.address), lower(coreg_x.address)) > 0.7
;
QUE
RY PLAN
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-----
Nested Loop (cost=0.01..640.49 rows=875 width=39) (actual time=9.990..53.839 rows=29 loo
ps=1)
Join Filter: (similarity(lower((appendable_24_x.address)::text), lower((coreg_x.address
)::text)) > 0.7::double precision)
-> Seq Scan on appendable_24_x (cost=0.00..1.80 rows=80 width=34) (actual time=0.009.
.0.111 rows=80 loops=1)
-> Index Scan using coreg_x_name on coreg_x (cost=0.01..7.95 rows=1 width=64) (actual
time=0.024..0.137 rows=44 loops=80)
Index Cond: ((lower((coreg_x.first_name)::text) = lower((appendable_24_x.first_na
me)::text)) AND (lower((coreg_x.last_name)::text) = lower((appendable_24_x.last_name)::tex
t)))
Filter: ((coreg_x.phone IS NOT NULL) OR (coreg_x.email IS NOT NULL))
Total runtime: 53.950 ms
(7 rows)
一些统计数据、配置参数和其他数据
- PostgreSQL 版本：8.4.4
- 操作系统：CentOS 版本 5.5（最终版）
- 文件系统：ext3
- 总可用内存：8GB
- 共享缓冲区 = 2GB
- 有效缓存大小 = 7200MB
- 完整的运行时配置通过
show all：http://pastie.org/1159746 - 最大的分区 coreg 表（coreg_j）大小：~4900MB
- 相应行数：~3200 万
- 相应的（first_name，last_name）索引大小：~1000MB
- 第二个分区表（appendable_24_j）大小：~1800kB
- appendable_24_j 中的行数：~25,000
答案1
只有足够的 RAM 来缓存数据库的一小部分，并且正如您发布的查询计划所示，以前访问的数据/相关索引的部分确实被缓存了。（Postgres 不缓存查询结果）。
53 毫秒的运行时间并不算太差，而且我也不确定未缓存数据上的 20 秒是否意味着 PG 选择了一个糟糕的查询计划。毕竟，仅有问题的索引就有 1GB 大，但查看慢速查询的分析输出会很有趣。
如果这是您的问题，您可以尝试调整规划器成本，看看这是否会对最坏情况的性能产生任何影响。
您可能还想稍微增加一下 maintentance_work_mem，即使这没有任何关系。