获取存储过程中执行语句的计划

通常，在存储过程或函数内部执行的语句不会显示在执行计划中。绕过此限制的一种方法是启用 contrib 模块 auto_explain，它提供了选项auto_explain.log_nested_statements。这将把详细的查询计划写入 PostgreSQL 日志文件。

未启用时的示例auto_explain.log_nested_statements启用

LOG:  duration: 110.095 ms  plan:
        Query Text: SELECT ts_body_property_value(13451);
        Result  (cost=0.00..0.26 rows=1 width=0)

启用时的示例auto_explain.log_nested_statements启用

STATEMENT:  SELECT ts_body_property_value(13451);
LOG:  duration: 0.123 ms  plan:
        Query Text: SELECT value_searchable
                FROM object_property op
          INNER JOIN class_property cp
                  ON (cp.class_property_id = op.class_property_id)
               WHERE op.object_version_id=p_object_version_id
                 AND cp.ts_record_type = 2
            ORDER BY cp.position
        Sort  (cost=13.20..13.20 rows=1 width=221)
          Sort Key: cp."position"
          ->  Nested Loop  (cost=0.29..13.19 rows=1 width=221)
                Join Filter: (op.class_property_id = cp.class_property_id)
                ->  Seq Scan on class_property cp  (cost=0.00..1.55 rows=1 width=8)
                      Filter: (ts_record_type = 2)
                ->  Index Scan using object_property_object_version_id_ix on object_property op  (cost=0.29..11.58 rows=5 width=221)
                      Index Cond: (object_version_id = 13451)
CONTEXT:  PL/pgSQL function ts_body_property_value(integer) line 6 at FOR over SELECT rows
LOG:  duration: 3.038 ms  plan:
        Query Text: SELECT ts_body_property_value(13451);
        Result  (cost=0.00..0.26 rows=1 width=0)