performance - 如何理解 EXPLAIN ANALYZE

Question

我不太熟悉查看 EXPLAIN ANALYZE 结果，我的查询速度太慢是个大问题。我试图阅读如何解释解释查询的结果，但我仍然不知道我应该寻找什么，以及可能有什么问题。我感觉某处有一些大红灯在闪烁，我只是没看到。

所以查询非常简单，看起来像这样:

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

结果是这样的:

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

一点背景:我在 Postgresql 9.1.6 上，在 Herokus 专用数据库上运行。我的数据库有大约 7.5Gb RAM， table 车包含 310 万行，大约 200 万行的 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;

有人看到一些明显的大问题吗？

编辑:

SELECT pg_relation_size('cars'), pg_total_relation_size('cars');

pg_relation_size:2058444800pg_total_relation_size:4900126720

SELECT pg_relation_size('index_cars_onsale_on_brand_and_model_name');

pg_relation_size:46301184

SELECT avg(pg_column_size(cars)) FROM cars limit 5000;

平均:636.9732567210792995

没有限制:

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

Answer 1

虽然对于像这样的简单计划没有那么有用，http://explain.depesz.com真的很有用。参见 http://explain.depesz.com/s/t4fi .请注意“统计信息”选项卡和“选项”下拉菜单。

本计划注意事项:

• 估计行数 (183) 与实际行数 (25) 相当。它不是数百倍，也不是 1。当涉及到行计数估计或“1 vs 非 1”问题时，您更感兴趣的是数量级。 (你甚至不需要“足够接近政府工作”的准确性——“足够接近军事承包会计”就可以了)。选择性估计和统计数据似乎是合理的。

• 它使用提供的两列部分索引(使用 index_cars_onsale_on_brand_and_model_name 进行索引扫描)，因此它符合部分索引条件。您可以在 Filter: has_auto_gear 中看到它。还显示了索引搜索条件。

• 鉴于表的行数意味着索引相当大，尤其是超过两列时，查询性能看起来还不错。匹配的行将是分散的，因此很可能每一行也需要阅读单独的页面。

我看不出有什么问题。不过，此查询可能会从 PostgreSQL 9.2 的仅索引扫描中受益匪浅。

这里可能有一些表膨胀，但考虑到 2 列索引和绝对行数，响应时间并非完全不合理，特别是对于具有 170 (!!) 列的表，可能适合相对较少的列元组到每一页。如果您可以承受一些停机时间，请尝试 VACUUM FULL 来重组表并重建索引。这将在重建表时专门锁定表一段时间。如果您无法承受停机时间，请参阅 pg_reorg和/或 CREATE INDEX CONCURRENTLY 和 ALTER INDEX ... RENAME TO。

您可能会发现 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 有时会提供更多信息，因为它可以显示缓冲区访问等。

可能使此查询更快的一个选项(尽管它冒着使其他查询稍微变慢的风险)是根据 brand 对表进行分区并启用 constraint_exclusion。参见 partitioning .