我在AWS RDS postgres物化视图上进行简单查询,返回速度很慢(2-3分钟)。我的工作假设是,这是一个网络传输问题,但我不是postgres的专家。关于设置的一些背景信息:
- "PostgreSQL 10.21 on x86_64-pc-linux-gnu,由x86_64-pc-linux-gnu- GCC (GCC) 7.4.0编译,64位",
- 在RDS, 16 Aurora Units的无服务器模式下运行
- 此过程通过AWS VPN连接运行
- 所讨论的表大约有50M行, 30列,目前总大小为20GB
- 我在视图上放置了一个多列索引(感兴趣的2列)。其中一个"复合"。有大约100K个唯一值,每个化合物大约有1000行,其他的"基因"。 具有约500个独特的值,每个基因的范围为100K-2M行。
当我运行
SELECT * FROM my_materialized_view where "COMPOUND" = 'cpd1234';
查询返回~3-5秒。
当我运行:
SELECT * FROM my_materialized_view where "GENE" = 'geneXYZ';
查询需要~2min来返回1M行。返回的数据大小为100-200MB。
做一些更"异国情调"的事情:
SELECT * FROM my_materialized_view where "GENE" = 'geneXYZ' ORDER BY 'measured_value' LIMIT 10000;
在大约12秒内返回数据。
我还跑了:
EXPLAIN ANALYZE SELECT * FROM my_materialized_view where "GENE" = 'geneXYZ';
输出似乎表明该过程约为500ms
在my_materialized_view上使用my_materialized_view_idx进行索引扫描(cost=0.56..1415484.25 rows=922393 width=336)(实际时间=0.039..437.332 rows=979434 loops=1)";索引编号:(";= ' geneXYZ ':文本),策划时间:0.092毫秒;执行时间:491.306毫秒
是我读错了还是数据传输/网络延迟问题?我使用pgadmin进行查询,但也通过python boto3运行查询,并实现了等效时间。感谢你的想法。谢谢!
关于您的情况的两个事实清楚地表明您的数据传输缓慢,而不是RDBMS服务器缓慢。
- 你在
GENE
列上有一个索引,并且你的查询计划说postgreSQL正在使用它。 - 你的第二个查询,它的
ORDER BY some_unindexed_column LIMIT some_number
,给postgreSQL增加了排序负担。它返回第一次查询数据的1%,并且完成速度提高了10倍。这意味着排序是有成本的,但是传输也有成本。