postgresql - 第一次查询慢

Question

我在对表执行第一个查询时遇到了麻烦。后续查询要快得多，即使我更改要查找的范围日期也是如此。我假设 PostgreSQL 实现了一种缓存机制，可以让后续查询更快。我可以尝试预热缓存，以便第一个用户请求可以命中缓存。但是，我认为我可以以某种方式改进以下查询:

SELECT
    y.id, y.title, x.visits, x.score
FROM (
    SELECT
        article_id, visits,
        COALESCE(ROUND((visits / NULLIF(hits ,0)::float)::numeric, 4), 0) score
    FROM (
        SELECT
            article_id, SUM(visits) visits, SUM(hits) hits
        FROM
            article_reports
        WHERE
            a.site_id = 'XYZ' AND a.date >= '2017-04-13'  AND a.date <= '2017-06-28'
        GROUP BY
            article_id
    ) q ORDER BY score DESC, visits DESC LIMIT(20)
) x 
INNER JOIN 
    articles y ON x.article_id = y.id

关于如何改进它的任何想法。下面是EXPLAIN的结果:

   Nested Loop  (cost=84859.76..85028.54 rows=20 width=272) (actual time=12612.596..12612.836 rows=20 loops=1)
  ->  Limit  (cost=84859.34..84859.39 rows=20 width=52) (actual time=12612.502..12612.517 rows=20 loops=1)
    ->  Sort  (cost=84859.34..84880.26 rows=8371 width=52) (actual time=12612.499..12612.503 rows=20 loops=1)
          Sort Key: q.score DESC, q.visits DESC
          Sort Method: top-N heapsort  Memory: 27kB
          ->  Subquery Scan on q  (cost=84218.04..84636.59 rows=8371 width=52) (actual time=12513.168..12602.649 rows=28965 loops=1)
                ->  HashAggregate  (cost=84218.04..84301.75 rows=8371 width=36) (actual time=12513.093..12536.823 rows=28965 loops=1)
                      Group Key: a.article_id
                      ->  Bitmap Heap Scan on article_reports a  (cost=20122.78..77122.91 rows=405436 width=36) (actual time=135.588..11974.774 rows=398242 loops=1)
                            Recheck Cond: (((site_id)::text = 'XYZ'::text) AND (date >= '2017-04-13'::date) AND (date <= '2017-06-28'::date))
                            Heap Blocks: exact=36911
                            ->  Bitmap Index Scan on index_article_reports_on_site_id_and_article_id_and_date  (cost=0.00..20021.42 rows=405436 width=0) (actual time=125.846..125.846 rows=398479 loops=1)"
                                  Index Cond: (((site_id)::text = 'XYZ'::text) AND (date >= '2017-04-13'::date) AND (date <= '2017-06-28'::date))
  ->  Index Scan using articles_pkey on articles y  (cost=0.42..8.44 rows=1 width=128) (actual time=0.014..0.014 rows=1 loops=20)
       Index Cond: (id = q.article_id)
 Planning time: 1.443 ms
 Execution time: 12613.689 ms

提前致谢

Answer 1

Postgres 使用了两级“缓存”:

• 操作系统文件缓存
• 共享缓冲区。

重要:Postgres 只直接控制第二个，并依赖第一个，它在操作系统的控制下。

我首先要检查的是 postgresql.conf 中的这两个设置:

• effective_cache_size – 通常我将其设置为所有可用 RAM 的 ~3/4。请注意，这不是告诉 Postgres 如何分配内存的设置，它只是给 Postgres 规划器的“建议”，告知操作系统文件缓存大小的一些估计值
• shared_buffers – 通常我将它设置为 RAM 大小的 1/4。这是分配设置。

另外，我会检查其他与内存相关的设置(work_mem、maintenance_work_mem)以了解可能会消耗多少 RAM，我的effective_cache_size 估计在大多数时候都是正确的。

但是如果你刚打开你的 Postgres，第一个查询很可能会很长，因为操作系统文件缓存和共享缓冲区中没有数据。您可以使用高级 EXPLAIN 选项检查它:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

-- 您将看到从磁盘(“读取”)或缓存(“命中”)中获取了多少缓冲区

在这里您可以找到有关使用 EXPLAIN 的好 Material :http://www.dalibo.org/_media/understanding_explain.pdf

此外，还有一个旨在解决“冷缓存”问题的扩展:pg_prewarm https://www.postgresql.org/docs/current/static/pgprewarm.html

此外，使用 SSD 磁盘而不是磁性磁盘意味着磁盘读取速度会快得多。

祝 Postgres 工作愉快 :-)