主页 / dba / 问题 / 301847
Accepted
Tom
Asked: 2021-10-29 12:46:22 +0800 CST

PostgreSQL 查询成本高

  • 772

我有一个包含超过 10.000.000 条记录的表，我正在创建一个返回大约 4436 条记录的查询。

碰巧它给我的印象是到达最后一条记录的查询成本非常高。

Index Scan using idx_name on task  (cost=0.28..142102.57 rows=3470 width=34) (actual time=14.690..22.894 rows=4436 loops=1)
"  Index Cond: ((situation = ANY ('{0,1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}'::integer[])) AND (deadline < CURRENT_TIMESTAMP))"
Planning Time: 1.335 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.654 ms, Inlining 0.000 ms, Optimization 1.214 ms, Emission 13.163 ms, Total 16.030 ms
Execution Time: 24.758 ms

这种成本水平是否可以接受，或者该指数是否需要改进？

指数：

CREATE INDEX idx_name ON task (situation, deadline, approved)
WHERE
deadline IS NOT NULL AND
situation <> ALL ('{4,5}'::integer[]) AND
approved = 'N';

我的查询：

SELECT
        task.deadline,
        task.id
    FROM
        task
    WHERE
        task.deadline IS NOT NULL
        AND task.situation IN ('0', '1', '2', '3', '6' ,'7' ,'8','9','10','11','12','13','14','15','16','17','18','19','20')
        AND task.situation NOT IN ('4', '5')
        AND task.deadline < CURRENT_TIMESTAMP
        AND task.approved = 'N';
postgresql postgresql-performance

3 个回答

  1. 正如评论中所暗示的那样，您不应该查看查询成本，而是查看其实际运行时间（或者，如果运行时间可以接受，您根本不应该被它所困扰）。除了与其他可能的计划相比， Postgres 估计执行此特定计划可能需要花费的各种资源的相对数量之外，估计的计划成本并不能说明任何事情。

    查看绝对成本值绝对不会告诉您任何信息。根据优化器拥有的信息，将其与其他计划的成本进行比较会告诉您 Postgres 认为哪个更有效。

    另请参阅此问答。

    看着马的嘴，人们可能会看到：

    /*-------------------------------------------------------------------------
 *
 * costsize.c
 *    Routines to compute (and set) relation sizes and path costs
 *
 * Path costs are measured in arbitrary units established by these basic
 * parameters:
 *
 *  seq_page_cost       Cost of a sequential page fetch
 *  random_page_cost    Cost of a non-sequential page fetch
 *  cpu_tuple_cost      Cost of typical CPU time to process a tuple
 *  cpu_index_tuple_cost  Cost of typical CPU time to process an index tuple
 *  cpu_operator_cost   Cost of CPU time to execute an operator or function
 *  parallel_tuple_cost Cost of CPU time to pass a tuple from worker to leader backend
 *  parallel_setup_cost Cost of setting up shared memory for parallelism
...

    并沿着这条路走得更远：

    # - Planner Cost Constants -

#seq_page_cost = 1.0            # measured on an arbitrary scale
#random_page_cost = 4.0         # same scale as above
#cpu_tuple_cost = 0.01          # same scale as above
#cpu_index_tuple_cost = 0.005       # same scale as above
#cpu_operator_cost = 0.0025     # same scale as above

    你会注意到他们在“任意尺度”上强调“任意单位”；他们想要确定的是，随机读取 N 个页面的资源密集型（“昂贵”）是顺序读取那么多页面的四倍，或者针对索引条目评估谓词的成本是针对表执行该操作的一半排。当整个计划树的成本加起来时，您得到的值只能与另一棵树的成本进行比较；它只能是高或低，不能高或低。

    HT to jjanes在评论中提到JIT。在您的情况下，估计的查询成本恰好超过 JIT 触发阈值，默认为 100000，并且正如 jjanes 敏锐地指出的那样，“[您的查询]时间的 2/3 似乎花费在 JIT 上”，这可能是适得其反。您可能想要评估在您的环境中启用 JIT 是否有用。

    • 6
  2. Best Answer

    索引好，查询快。

    但是索引可以更好，查询更快。索引列approved只是带有条件的死运费approved = 'N'。去掉它。

    CREATE INDEX idx_name ON task (situation, deadline)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

    索引大小很重要，即使approved是varchar(1)（并且可能应该是boolean）。由于deadline是（对齐的）timestamp（with time zone）类型，添加的索引列approved每个索引元组至少浪费 8 个字节 - 使其增长 1/3。

    更好的是，如果示例有SELECT deadline, id ...任何指示，则应该将其附加id为“包含”列以允许 仅索引扫描：

    CREATE INDEX idx_name ON task (situation, deadline) INCLUDE (id)
WHERE  deadline IS NOT NULL
AND    situation <> ALL ('{4,5}'::integer[])
AND    approved = 'N';

    需要 Postgres 11 或更高版本。将大小恢复到以前的大小。

    看：

    • 4

  3. 其他两个答案都很好，让我补充一个细节：

    您的两个条件都不是简单的相等比较，因此索引中的第二列将仅用于过滤掉行（在访问表之前）。因此索引列的顺序将影响必须读取多少索引条目才能找到答案。

    要找到查询的最佳索引，首先尝试使用一个索引 on (situation, deadline)，然后删除该索引并尝试使用一个 on (deadline, situation)，然后查看其中哪个索引与 接触较少的块EXPLAIN (ANALYZE, BUFFERS)。

    • 3

相关问题