PostgreSQL第一次查询慢

Question

我实现了光标分页。对于第一行，它工作得很好但越往下滚动，第一个查询发送就越慢。我运行这个查询:

SELECT *
FROM "movie" "m"
INNER JOIN "movie_stats" "ms" ON "m"."uuid" = "ms"."movie_uuid"
WHERE (((("ms"."data"->'stat'->'overall'->>'total')::FLOAT), "ms"."movie_uuid") < (74.566, '50bca81c-4676-403e-8314-c721ba67646c')) AND ("m"."status" != 'deleted')
ORDER BY (("ms"."data"->'stat'->'overall'->>'total')::FLOAT) DESC NULLS LAST, "ms"."movie_uuid" DESC NULLS LAST
LIMIT 40

第一次运行查询后的执行时间为444ms:

QUERY PLAN
Limit  (cost=0.84..154.18 rows=40 width=565) (actual time=9.171..444.788 rows=40 loops=1)"
->  Nested Loop  (cost=0.84..506620.20 rows=132160 width=565) (actual time=9.169..444.735 rows=40 loops=1)"
->  Index Scan using movie_stats_stat_overall_score_idx on movie_stats ""ms""  (cost=0.42..165910.17 rows=132443 width=741) (actual time=9.078..276.405 rows=40 loops=1)"
Index Cond: (ROW(((((data -> 'stat'::text) -> 'overall'::text) ->> 'total'::text))::double precision, movie_uuid) < ROW('74.566'::double precision, '50bca81c-4676-403e-8314-c721ba67646c'::uuid))"
->  Index Scan using movie_pkey on movie m  (cost=0.42..2.56 rows=1 width=541) (actual time=4.188..4.188 rows=1 loops=40)"
Index Cond: (uuid = ""ms"".movie_uuid)"
Filter: (status <> 'deleted'::movie_status)"
Planning time: 1.140 ms
Execution time: 444.943 ms

但是在第二次执行相同的查询后，执行时间只有1ms:

QUERY PLAN
Limit  (cost=0.84..154.18 rows=40 width=1314) (actual time=0.066..0.791 rows=40 loops=1)"
->  Nested Loop  (cost=0.84..506620.20 rows=132160 width=1314) (actual time=0.064..0.776 rows=40 loops=1)"
->  Index Scan using movie_stats_stat_overall_score_idx on movie_stats ""ms""  (cost=0.42..165910.17 rows=132443 width=749) (actual time=0.030..0.120 rows=40 loops=1)"
Index Cond: (ROW(((((data -> 'col'::text) -> 'overall'::text) ->> 'total'::text))::double precision, movie_uuid) < ROW('74.566'::double precision, '50bca81c-4676-403e-8314-c721ba67646c'::uuid))"
->  Index Scan using movie_pkey on movie m  (cost=0.42..2.56 rows=1 width=541) (actual time=0.011..0.011 rows=1 loops=40)"
Index Cond: (uuid = ""asc"".movie_uuid)"
Filter: (status <> 'deleted'::movie_status)"
Planning time: 1.252 ms
Execution time: 0.916 ms

接下来的每40行都是这样。有人能给我解释一下吗?谢谢你的帮助!

Answer 1

第一次执行可能需要从磁盘中获取数据，而第二次执行已经在共享缓冲区中找到了数据。您可以使用EXPLAIN (ANALYZE, BUFFERS)进行诊断，它将显示在缓存(hit)中找到的8kb块的数量，并从磁盘(read)中读取。

例如:

Seq Scan on tab  (...) (actual time=0.353..126.805 ...)
Buffers: shared read=1959

和

Seq Scan on tab  (...) (actual time=0.011..21.471 ...)
Buffers: shared hit=1959

如果你需要加快速度，有两种可能:

1. 不要SELECT *，把查询中需要的所有列都放到索引中。然后，您可以获得一个仅针对索引的扫描，如果您对它进行VACUUM，它可能根本不会命中表。

   当然，如果您需要很多列，这可能是不可行的。

2. 获取更多内存并尝试将表保存在缓存中。pg_prewarm可能有帮助。