题
我怎样才能使这个post中描述的查询更快，尤其是通过使用可用的RAM来制作PostgreSQL？ – 请注意，我试图适当地configurationeffective_cache_size和shared_buffers。 见下文。
背景
我必须定期join一个约2.6亿行表（coreg_master），并附带新的数据。我已经对表进行分区，以允许每个分区适合内存。 我当然也有适当的索引。 但是，将分区表分别与其他（小得多）表连接时，会在磁盘上执行完全随机的IO操作。 这是由于在大型表上嵌套循环索引扫描，由于我们没有很好的磁盘设置，所以速度很慢。
我希望它使用所有可用的内存来caching大的分区表，我知道应该由Linux内核/文件系统本身来完成。 但它仍然不会将表加载到RAM中，尽pipe它适合。 我想这是因为访问模式是不连续的，因此不会触发caching？ 我不知道。 查询计划和configuration参数如下。
表结构
这是我的大桌子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等等一起被join。
\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列的第一个字母进行分区）的输出，因为它不需要太长时间。 但是，查询计划对于每个分区上的所有连接都是相同的，所以它应该代表较大的连接（注意，我对表进行了ANALYZE ，总时间实际上是20秒，但是在这里更快结果被caching）：
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)
一些统计数据，configuration参数和其他数据
show all完整的运行时configuration： http ： //pastie.org/1159746 只有足够的内存来caching你的数据库的一小部分，并作为你发布的查询计划显示，以前访问的索引数据/索引的部分确实被caching。 （Postgres不会caching查询结果）。
53 ms的运行时间不是太简陋，我不确定20秒的未caching数据意味着PGselect了一个坏的查询计划。 毕竟，所讨论的索引只有1GB大，但如果查看分析输出为慢速查询将会很有趣。
如果这是你的问题，你可以尝试调整计划成本，看看是否对最坏情况的performance有影响。
你可能也想增加一下你的maintentance_work_mem，即使这是没有关系的。