sql - Postgresql:如何确保索引在内存中

Question

我一直在尝试在 Windows Server 2012 上的 Azure VM 上运行 postgres 9.3。我最初是在 7GB 服务器上运行它……我现在在 14GB Azure VM 上运行它。在尝试解决下面描述的问题时，我加大了尺寸。

顺便说一句，我对 posgresql 很陌生，所以我只是一点一点地了解配置选项。此外，虽然我很想在 Linux 上运行它，但我和我的同事根本不具备在 Linux 出现问题时解决问题的专业知识，因此 Windows 是我们唯一的选择。

问题描述:

我有一个名为 test_table 的表；它目前存储了大约 9000 万行。它将每月增长大约 3-4 百万行。 test_table 中有 2 列:

id (bigserial)
url (charachter varying 300)

我在从几个 CSV 文件导入数据后创建了索引。两列都已编入索引.... id 是主键。 url 上的索引是通过 pgAdmin 使用默认值创建的普通 btree。

当我运行时:

SELECT sum(((relpages*8)/1024)) as MB FROM pg_class WHERE reltype=0;

...总大小为5980MB

这里涉及到的2个索引各自的大小如下，我是通过运行得到的:

 # SELECT relname, ((relpages*8)/1024) as MB, reltype FROM pg_class WHERE 
  reltype=0 ORDER BY relpages DESC LIMIT 10;


             relname      |  mb  | reltype
----------------------------------+------+--------
 test_url_idx             | 3684 |       0
 test_pk                  | 2161 |       0

其他较小的表上还有其他索引，但它们很小(< 5MB)....所以我在这里忽略它们

使用 url 查询 test_table 时，特别是在搜索中使用通配符时，问题在于速度(或缺乏速度)。例如

select * from test_table where url like 'orange%' limit 20;

...运行需要 20-40 秒。

对上面的内容运行 explain analyze 得到以下结果:

# explain analyze select * from test_table where
   url like 'orange%' limit 20;

          QUERY PLAN
-----------------------------------------------------------------    
 Limit  (cost=0.00..4787.96 rows=20 width=57) 
     (actual time=0.304..1898.583 rows=20 loops=1)
   ->  Seq Scan on test_table  (cost=0.00..2303247.60 rows=9621 width=57)
     (actual time=0.302..1898
    .542 rows=20 loops=1)
     Filter: ((url)::text ~~ 'orange%'::text)
     Rows Removed by Filter: 210286
    Total runtime: 1898.650 ms
  (5 rows)

再举一个例子……这次是 american 和 .com 之间的通配符……

# explain  select * from test_table where url 
   like 'american%.com' limit 50;

QUERY PLAN
-------------------------------------------------------
 Limit  (cost=0.00..11969.90 rows=50 width=57)
  ->  Seq Scan on test_table  (cost=0.00..2303247.60 rows=9621 width=57)
     Filter: ((url)::text ~~ 'american%.com'::text)
    (3 rows)


# explain analyze select * from test_table where url 
    like 'american%.com' limit 50;

QUERY PLAN
-----------------------------------------------------
 Limit  (cost=0.00..11969.90 rows=50 width=57) 
    (actual time=83.470..3035.696 rows=50      loops=1)
    ->  Seq Scan on test_table  (cost=0.00..2303247.60 rows=9621 width=57) 
            (actual time=83.467..303
  5.614 rows=50 loops=1)
     Filter: ((url)::text ~~ 'american%.com'::text)
     Rows Removed by Filter: 276142
 Total runtime: 3035.774 ms
(5 rows)

然后我从 7GB 的服务器升级到 14GB 的服务器。查询速度也好不到哪里去。

服务器上的观察

• 我可以看到内存使用量从未真正超过 2MB。
• 使用 LIKE 语句运行查询时，磁盘读取量超出了图表。
• 匹配 id(主键)时查询速度非常好

postgresql.conf 文件与默认值相比只有几处变化。请注意，我从以下博客文章中获取了其中一些建议:http://www.gabrielweinberg.com/blog/2011/05/postgresql.html .

对 conf 的更改:

shared_buffers = 512MB  

checkpoint_segments = 10 

(我更改了 checkpoint_segments，因为我在加载 CSV 文件时收到很多警告...尽管生产数据库不会非常写入密集型，因此如果需要可以将其改回 3...)

cpu_index_tuple_cost = 0.0005       
effective_cache_size = 10GB    # recommendation in the blog post was 2GB...

在服务器本身的任务管理器 -> 性能选项卡中，以下可能是可以提供帮助的相关位:

CPU:很少超过 2%(无论运行什么查询......当我导入一个 6GB CSV 文件时它达到 11%)

内存:1.5/14.0GB (11%)

关于内存的更多细节:

• 使用中:1.4GB
• 可用:12.5GB
• promise 1.9/16.1 GB
• 缓存:835MB
• 分页池:95.2MB
• 非分页池:71.2 MB

问题

1. 我怎样才能确保索引在内存中(前提是它不会太大而无法占用内存)？我在这里需要的只是配置调整吗？
2. 在这里实现我自己的搜索索引(例如 Lucene)是更好的选择吗？
3. 即使我可以解决内存中的索引问题，postgres 中的全文索引功能是否会显着提高性能？

感谢阅读。

Answer 1

这些 seq 扫描让您看起来好像在导入数据后没有在表上运行 analyze。

http://www.postgresql.org/docs/current/static/sql-analyze.html

在正常操作期间，安排运行 vacuum analyze 没有用，因为 autovacuum 会定期启动。但在进行大量写入时(例如导入期间)，这很重要。

关于一个稍微相关的说明，请参阅 Pavel 的 PostgreSQL 技巧站点上的反向索引提示，如果您需要在末尾而不是在开头运行锚定查询，例如喜欢'%.com'

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_I#section_20

---

关于您的实际问题，请注意您喜欢的帖子中的一些建议充其量是可疑的。更改索引使用的成本经常是可疑的，禁用 seq 扫描是彻头彻尾的愚蠢。 (有时，seq 扫描表比使用索引更便宜。)

话虽这么说:

1. Postgres 主要根据索引的使用频率来缓存索引，如果统计数据表明它不应该使用索引，它就不会使用索引——因此需要在导入后进行分析。为 Postgres 提供足够的内存当然也会增加它在内存中的可能性，但请记住后一点。
2. 和 3. 全文搜索工作正常。

有关微调的进一步阅读，请参阅手册和:

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

关于您的架构的最后两个注释:

1. 我最后检查了一下，bigint(在你的例子中是 bigserial)比纯 int 慢。 (这是很久以前的事了，所以现在在现代 64 位服务器上差异可以忽略不计。)除非您预见到您实际上需要超过 23 亿个条目，否则 int 足够了并且占用的空间更少。
2. 从实现的角度来看，varchar(300) 和没有指定长度(或 text，例如那件事)是对长度的额外检查约束。如果您实际上并不需要数据来适应该大小并且只是出于习惯而无缘无故地这样做，那么通过摆脱该约束，您的数据库插入和更新将运行得更快。