我有一个名为modifications
的表,它有42列84M行。总大小为64GB。
我在亚马逊RDS上运行Postgres 9.6.11,在db.m4.xlarge实例上有16GB的RAM。
当我运行一个简单的SELECT count(*) FROM modifications;
时,它需要380秒才能完成执行。
当我运行SELECT * FROM modifications WHERE post_date = '2016-05-03';
以限制为单个日期时,需要156秒才能返回结果中的4.6M行。
当我将结果集进一步限制为大约1M行时,查询仍然需要100多秒才能完成。
我知道这些都是大的结果集,但我对数据库查询性能测试还是个新手,所以我想了解一下该尝试什么。
我已经对这些查询运行了EXPLAIN ANALYZE
,但我不确定该怎么办。其中许多查询非常简单,没有明确的方法来重组它们以提高性能。
我还尝试添加更多的索引……我对每个最常见的查询列都有索引。
我使用AWS RDS PostgreSQL配置的默认设置,并尝试使用SET LOCAL work_mem = 'XXXMB'
调整work_mem
设置。这并没有什么不同。shared_buffers
(0.5GB(和effective_cache_size
(0.5GB。
如有任何关于如何进行故障排除的建议或策略,我们将不胜感激。如果我需要更多信息,请在评论中告诉我。
编辑:这是最后一个SELECT
查询的执行计划
Bitmap Heap Scan on modifications (cost=479407.01..1692971.07 rows=460492 width=279)
Recheck Cond: ((post_date = '2016-05-03 00:00:00'::timestamp without time zone) AND (change_type = 'residence_address_line_1'::text))
-> BitmapAnd (cost=479407.01..479407.01 rows=460492 width=0)
-> Bitmap Index Scan on modifications_post_date_idx (cost=0.00..130733.87 rows=4478040 width=0)
Index Cond: (post_date = '2016-05-03 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on modifications_change_type_idx (cost=0.00..348442.64 rows=8677610 width=0)
Index Cond: (change_type = 'residence_address_line_1'::text)
您应该打开track_io_timing,然后执行EXPLAIN (ANALYZE, BUFFERS)
来查看查询的性能。
对于您显示其计划的查询,最好在(change_type, post_date)
上有多列索引。但是,使用数百个多列索引来支持数百个不同的查询是不可行的。因此,您应该同时查看具有多列索引和两个单列索引的查询的EXPLAIN (ANALYZE, BUFFERS)
。
您列出了3个截然不同的查询。哪一个是你最关心的?您通常需要优化查询,以获得所需的结果,不能根据优化的容易程度在不同的查询中进行选择。