postgresql 如何理解解释分析
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12915209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to understand an EXPLAIN ANALYZE
提问by Niels Kristian
I am not very familiar with looking at EXPLAIN ANALYZE results, I have a huge problem with my queries being too slow. I have tried to read up on how to interpret results from an explain queries, but I still don't know what I should be looking for, and what might be wrong. I have a feeling that there is some big red light flashing somewhere, I just don't see it.
我对查看 EXPLAIN ANALYZE 结果不是很熟悉，我的查询太慢有一个很大的问题。我试图阅读如何解释解释查询的结果，但我仍然不知道我应该寻找什么，以及可能有什么问题。我有一种感觉，某处有一些大红灯在闪烁，我只是没有看到。
So the query is pretty simple, it looks like this:
所以查询非常简单，它看起来像这样：
EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."brand" = 'BMW' AND "cars"."model_name" = '318i' AND "cars"."has_auto_gear" = TRUE LIMIT 25 OFFSET 0
And the result like this:
结果是这样的：
Limit (cost=0.00..161.07 rows=25 width=1245) (actual time=35.232..38.694 rows=25 loops=1)
-> Index Scan using index_cars_onsale_on_brand_and_model_name on cars (cost=0.00..1179.06 rows=183 width=1245) (actual time=35.228..38.652 rows=25 loops=1)
Index Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text))
Filter: has_auto_gear"
Total runtime: 38.845 ms
A little background: I'm on Postgresql 9.1.6, running on Herokus dedicated databases. My db has aprox 7,5Gb RAM, the table cars contains 3,1M rows and an aprox 2,0M of the rows has sales_state = 'onsale'. The table has 170 columns. The index that it uses looks something like this:
一点背景：我使用的是 Postgresql 9.1.6，在 Herokus 专用数据库上运行。我的数据库有大约 7,5Gb RAM，表汽车包含 3,1M 行，大约 2,0M 行有 sales_state = 'onsale'。该表有 170 列。它使用的索引如下所示：
CREATE INDEX index_cars_onsale_on_brand_and_model_name
ON cars
USING btree
(brand COLLATE pg_catalog."default" , model_name COLLATE pg_catalog."default" )
WHERE sales_state::text = 'onsale'::text;
Anyone seeing some big obvious issue?
有人看到一些明显的大问题吗？
EDIT:
编辑：
SELECT pg_relation_size('cars'), pg_total_relation_size('cars');
pg_relation_size: 2058444800 pg_total_relation_size: 4900126720
pg_relation_size: 2058444800 pg_total_relation_size: 4900126720
SELECT pg_relation_size('index_cars_onsale_on_brand_and_model_name');
pg_relation_size: 46301184
pg_relation_size: 46301184
SELECT avg(pg_column_size(cars)) FROM cars limit 5000;
avg: 636.9732567210792995
平均：636.9732567210792995
WITHOUT THE LIMIT:
没有限制：
EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."brand" = 'BMW' AND "cars"."model_name" = '318i' AND "cars"."has_auto_gear" = TRUE
Bitmap Heap Scan on cars (cost=12.54..1156.95 rows=183 width=4) (actual time=17.067..55.198 rows=2096 loops=1)
Recheck Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text) AND ((sales_state)::text = 'onsale'::text))
Filter: has_auto_gear
-> Bitmap Index Scan on index_cars_onsale_on_brand_and_model_name (cost=0.00..12.54 rows=585 width=0) (actual time=15.211..15.211 rows=7411 loops=1)"
Index Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text))
Total runtime: 56.851 ms
采纳答案by Craig Ringer
While not as useful for a simple plan like this, http://explain.depesz.comis really useful. See http://explain.depesz.com/s/t4fi. Note the "stats" tab and the "options" pulldown.
虽然对于像这样的简单计划没有那么有用，但http://explain.depesz.com确实很有用。请参阅http://explain.depesz.com/s/t4fi。注意“统计”选项卡和“选项”下拉菜单。
Things to note about this plan:
此计划的注意事项：
The estimated row count (183) is reasonably comparable to the actual row count (25). It's not hundreds of times more, nor is it 1. You're more interested in orders of magnitude when it comes to rowcount estimates, or "1 vs not 1" issues. (You don't even need "close enough for government work" accuracy - "close enough for military contracting accounting" will do). The selectivity estimate and statistics seem reasonable.
It's using the two-column partial index provided (
index scan using index_cars_onsale_on_brand_and_model_name), so it's matched the partial index condition. You can see that in theFilter: has_auto_gear. The index search condition is also shown.The query performance looks reasonable given that the table's row count will mean the index is fairly big, especially as it's over two columns. Matching rows will be scattered, so it's likely each row will require a separate page read too.
估计行数 (183) 与实际行数 (25) 相当。它不是数百倍，也不是 1。当涉及到行数估计或“1 与非 1”问题时，您对数量级更感兴趣。（你甚至不需要“足够接近政府工作”的准确性——“足够接近军事合同会计”就可以了）。选择性估计和统计似乎是合理的。
它使用提供的两列部分索引 (
index scan using index_cars_onsale_on_brand_and_model_name)，因此它与部分索引条件匹配。你可以在Filter: has_auto_gear. 还显示了索引搜索条件。考虑到表的行数意味着索引相当大，尤其是当它超过两列时，查询性能看起来很合理。匹配的行将分散，因此每行可能也需要单独的页面读取。
I see nothing wrong here. This query will likely benefit greatly from PostgreSQL 9.2's index-only scans, though.
我看这里没有任何问题。不过，该查询可能会从 PostgreSQL 9.2 的仅索引扫描中受益匪浅。
It's possible there's some table bloat here, but given the 2-column index and the sheer number of rows the response time isn't entirely unreasonable, especially for a table with 170 (!!) columns that's likely to fit relatively few tuples into each page. If you can afford some downtime try VACUUM FULLto reorganize the table and rebuild the index. This will exclusively lock the table for some time while it rebuilds it. If you can't afford the downtime, see pg_reorgand/or CREATE INDEX CONCURRENTLYand ALTER INDEX ... RENAME TO.
这里可能存在一些表膨胀，但考虑到 2 列索引和大量行，响应时间并非完全不合理，特别是对于具有 170 (!!) 列的表，每个列可能适合相对较少的元组页。如果您能承受一些停机时间，请尝试VACUUM FULL重新组织表并重建索引。这将在重建表时独占锁定该表一段时间。如果您负担不起停机时间，请参阅pg_reorg和/或CREATE INDEX CONCURRENTLY和ALTER INDEX ... RENAME TO。
You might find EXPLAIN (ANALYZE, BUFFERS, VERBOSE)more informative sometimes, as it can show buffer accesses, etc.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)有时您可能会发现更多信息，因为它可以显示缓冲区访问等。
One option that may make this query faster (though it runs the risk of slowing other queries somewhat) is to partition the table on brandand enable constraint_exclusion. See partitioning.
可能使此查询更快的一种选择（尽管它会在一定程度上降低其他查询的速度）是对表进行分区brand并启用constraint_exclusion. 请参阅分区。
回答by Wolph
Well... the first thing I can tell you is that your database is expecting (from the statistics) to get 183 rows. In reality it is getting 25 rows. Although that is probably not too relevant in this case (i.e. with these small amounts and no heavy operations, don't have to worry about estimating it wrongly).
嗯...我可以告诉你的第一件事是你的数据库期望（从统计数据）获得 183 行。实际上它有 25 行。尽管在这种情况下这可能不太相关（即，这些少量且没有繁重的操作，不必担心估计错误）。
A bigger problem (imho) is that a simple index lookup for 25 rows is taking 35ms. That seems a bit much. Is the database heavy enough to have at least all indexes in memory? It is not excessive though, just seems a bit slow for me.
一个更大的问题（恕我直言）是 25 行的简单索引查找需要 35 毫秒。这似乎有点多。数据库是否足够重以在内存中至少包含所有索引？但这并不过分，对我来说似乎有点慢。
As for looking at your explains, I would recommend using explain.depesz.com: http://explain.depesz.com/s/sA6
至于看你的解释，我会建议使用explain.depesz.com：http://explain.depesz.com/s/sA6
