postgresql - 为什么此查询使用了错误的索引？
问题描述
我目前在同一查询的两个不同主机（本地和远程）上使用 Postgresql 的索引存在问题。有问题的查询如下：
SELECT COUNT(*)
FROM (
SELECT 1 AS one
FROM "big_table"
WHERE "big_table"."user_id" = 13
AND "big_table"."action" = 1
AND (big_table.created_at >= '2018-12-09 23:00:00'::timestamp without time zone)
ORDER BY big_table.created_at desc LIMIT 15 OFFSET 10
) subquery_for_count;
更改此查询超出了我的范围，因为它是由我们正在使用的库生成的，所以我想找到一个解决方案而不必更改它。如果我EXPLAIN使用所述查询在本地运行命令，我的 Postgres 实例将输出以下内容：
local_host=# EXPLAIN SELECT COUNT(*) FROM (SELECT 1 AS one FROM "big_table" WHERE "big_table"."user_id" = 13 AND "big_table"."action" = 1 AND (big_table.created_at >= '2018-12-09 23:00:00'::timestamp without time zone) ORDER BY big_table.created_at desc LIMIT 15 OFFSET 10) subquery_for_count;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.59..8.60 rows=1 width=8)
-> Limit (cost=8.57..8.58 rows=1 width=12)
-> Sort (cost=8.57..8.57 rows=1 width=12)
Sort Key: big_table.created_at DESC
-> Index Scan using big_table_idx_user_action_transfers on big_table (cost=0.56..8.56 rows=1 width=12)
Index Cond: ((user_id = 13) AND (action = 1))
Filter: (created_at >= '2018-12-09 23:00:00'::timestamp without time zone)
(7 rows)
这很好，它（部分）按预期使用复合user_id索引action。但是，如果我在远程系统上运行查询，我会得到以下EXPLAIN输出：
remote_host=# EXPLAIN SELECT COUNT(*) FROM (SELECT 1 AS one FROM "big_table" WHERE "big_table"."user_id" = 13 AND "big_table"."action" = 1 AND (big_table.created_at >= '2018-12-09 23:00:00'::timestamp without time zone) ORDER BY big_table.created_at desc LIMIT 15 OFFSET 10) subquery_for_count;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8472.67..8472.68 rows=1 width=8)
-> Limit (cost=3389.25..8472.48 rows=15 width=12)
-> Index Scan Backward using index_big_table_on_created_at on big_table (cost=0.44..4492554.51 rows=13257 width=12)
Index Cond: (created_at >= '2018-12-09 23:00:00'::timestamp without time zone)
Filter: ((user_id = 13) AND (action = 1))
(5 rows)
可以看到，在远程主机上，数据库使用索引 on created_at，而不是user_id和action作为我的本地安装。这导致此查询在远程主机上非常缓慢（> 1 分钟才能完成），因为有很多条目满足索引条件，并且过滤所有这些条目需要很多时间。但是，在我的本地安装中，它非常快（大约 1 秒即可完成）。我的本地表和远程表都具有相同数量的条目（~25mio。）和大致相同的数据分布。我们在远程主机上运行 Vacuum 守护程序，所以VACUUM ANALYZE经常这样做。此外，两个系统上的索引设置完全相同。
我已经尝试为这个问题寻找解决方案，但到目前为止，除了运行VACUUM ANALYZE并确保相关属性的索引存在之外，我还没有发现任何有用的东西。
也许你们中的一个人有线索？当然，我可以为所有使用的属性（ 和 ）添加复合索引user_id，action但created_at我仍然对为什么在这种情况下不在远程主机上使用“正确”索引感到非常困惑。
两台主机都使用 9.6 版的 Postgres（准确地说是9.6.9在本地主机和9.6.17远程主机上）。
编辑#1：这是(ANALYZE, BUFFERS)在生产主机上设置的查询计划的输出：
remote_host=# EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM (SELECT 1 AS one FROM "big_table" WHERE "big_table"."user_id" = 874 AND "big_table"."action" = 1 AND (big_table.created_at >= '2018-12-09 23:00:00') ORDER BY big_table.created_at desc LIMIT 15 OFFSET 10) subquery_for_count;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8539.91..8539.92 rows=1 width=8) (actual time=206301.833..206301.833 rows=1 loops=1)
Buffers: shared hit=16082155 read=550889 dirtied=3
-> Limit (cost=3416.15..8539.73 rows=15 width=12) (actual time=206301.826..206301.826 rows=0 loops=1)
Buffers: shared hit=16082155 read=550889 dirtied=3
-> Index Scan Backward using index_big_table_on_created_at on big_table (cost=0.44..4515235.12 rows=13219 width=12) (actual time=67472.247..206301.821 rows=2 loops=1)
Index Cond: (created_at >= '2018-12-09 23:00:00'::timestamp without time zone)
Filter: ((user_id = 874) AND (action = 1))
Rows Removed by Filter: 26362926
Buffers: shared hit=16082155 read=550889 dirtied=3
Planning time: 2.003 ms
Execution time: 206302.505 ms
(11 rows)
编辑#2：这是(ANALYZE, BUFFERS)在本地主机上设置的查询计划的输出：
local_host=# EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM (SELECT 1 AS one FROM "big_table" WHERE "big_table"."user_id" = 874 AND "big_table"."action" = 1 AND (big_table.created_at >= '2018-12-09 23:00:00') ORDER BY big_table.created_at desc LIMIT 15 OFFSET 10) subquery_for_count;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6.37..6.38 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1)
Buffers: shared hit=4
-> Limit (cost=6.35..6.35 rows=1 width=12) (actual time=0.043..0.043 rows=0 loops=1)
Buffers: shared hit=4
-> Sort (cost=6.34..6.35 rows=1 width=12) (actual time=0.041..0.041 rows=0 loops=1)
Sort Key: big_table.created_at DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4
-> Index Scan using big_table_idx_user_action_transfers on big_table (cost=0.56..6.33 rows=1 width=12) (actual time=0.022..0.022 rows=0 loops=1)
Index Cond: ((user_id = 874) AND (action = 1))
Filter: (created_at >= '2018-12-09 23:00:00'::timestamp without time zone)
Buffers: shared hit=4
Planning time: 0.299 ms
Execution time: 0.104 ms
(14 rows)
解决方案
它可以使用一个索引来提供过滤器，然后进行排序。或者它可以使用其他提供ORDER BY，然后根据LIMIT提前停止。它必须选择，因为它不能两者兼得。PostgreSQL 无法知道所有 with"big_table"."user_id" = 13 AND "big_table"."action" = 1也是很久以前创建的，因此它不知道基于 LIMIT 的提前停止实际上不会很早就停止。
很难弄清楚你的问题是什么。您似乎知道答案是什么，在(user_id, action, created_at). 如果您想解决性能问题，请这样做。
您说您很困惑，并且您无法更改查询。能够更改来自您的应用程序的查询与解决您的困惑无关。即使不幸的工具限制使您无法实施解决方案，但这并不妨碍您理解解决方案或问题。
您是在寻求理解，还是寻求解决方案？
我的本地表和远程表都具有相同数量的条目（~25mio。）和大致相同的数据分布
数据分布有很多维度。也许它们在某些方面相似，但在其他方面则不然。查看两台服务器的输出EXPLAIN (ANALYZE, BUFFERS)确实会有所帮助，但可能还不够。EXPLAIN (ANALYZE, BUFFERS)当它使用快速计划时，从慢速服务器上看到它也很不错。您可以通过删除错误的索引或更改查询以使其使用ORDER BY (big_table.created_at + interval '0') desc. 您不需要让您的应用程序运行此查询，您可以手动运行它。
再三考虑，查看EXPLAIN (ANALYZE, BUFFERS)运行慢速计划的快速服务器可能会更有用。您可以通过更改要使用的查询来做到这一点...WHERE ("big_table"."user_id" + 0 = 13) AND...