postgresql - 使用参数而不是硬编码字符串时，Postgres 查询非常慢

Question

我遇到了这个 Postgres 问题，如果我使用参数而不是在查询字符串上硬编码它的值，则相同的查询需要很长时间才能执行。列名是“media_type”，它是一个 VARCHAR(20)。我正在使用 Symfony2 和 Doctrine2 ORM 从 PHP 运行这些查询，所讨论的表有大约 1.000.000 条记录。

我的查询有问题吗？会不会是 Postgres 配置问题？

1 - media_type 的硬编码值

duration: 5.365 ms  parse pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
duration: 0.142 ms  bind pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
parameters: $1 = '1', $2 = '1', $3 = '100', $4 = '0'
duration: 8.667 ms  execute pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
parameters: $1 = '1', $2 = '1', $3 = '100', $4 = '0'

执行计划:

duration: 8.640 ms  plan:
    Query Text: SELECT id,site_id FROM item where media_type = 'Collection' AND enabled = 'true' AND site_id = $1 AND user_id = $2 ORDER BY id DESC LIMIT $3 OFFSET $4
    Limit  (cost=8.38..8.38 rows=1 width=12) (actual time=8.516..8.595 rows=24 loops=1)
      Buffers: shared hit=10 read=15
        ->  Sort  (cost=8.38..8.38 rows=1 width=12) (actual time=8.505..8.530 rows=24 loops=1)
            Sort Key: id
            Sort Method: quicksort  Memory: 26kB
            Buffers: shared hit=10 read=15
            ->  Index Scan using item_media_type_index on item  (cost=0.00..8.37 rows=1 width=12) (actual time=7.955..8.397 rows=24 loops=1)
                    Index Cond: ((media_type)::text = 'Collection'::text)
                    Filter: (enabled AND (site_id = $1) AND (user_id = $2))
                    Buffers: shared hit=8 read=15

2 - 使用 media_type 参数(较慢)

duration: 5.557 ms  parse pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
duration: 1.322 ms  bind pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
parameters: $1 = 'Collection', $2 = '1', $3 = '1', $4 = '100', $5 = '0'
duration: 71564.998 ms  execute pdo_stmt_00000001: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
parameters: $1 = 'Collection', $2 = '1', $3 = '1', $4 = '100', $5 = '0'

执行计划:

duration: 71564.922 ms  plan:
    Query Text: SELECT id,site_id FROM item where media_type = $1 AND enabled = 'true' AND site_id = $2 AND user_id = $3 ORDER BY id DESC LIMIT $4 OFFSET $5
    Limit  (cost=90663.16..181326.31 rows=17184 width=12) (actual time=3.667..71564.864 rows=24 loops=1)
      Buffers: shared hit=183786 read=96585
        ->  Index Scan Backward using item_pkey on item  (cost=0.00..906610.46 rows=171836 width=12) (actual time=3.655..71564.798 rows=24 loops=1)
               Filter: (enabled AND ((media_type)::text = $1) AND (site_id = $2) AND (user_id = $3))
               Buffers: shared hit=183786 read=96585

提前致谢。

Answer 1

这是 PostgreSQL 中一个长期存在的缺陷，过去需要一些有趣的规划器调整才能解决。它已在 PostgreSQL 9.2(现在处于测试阶段)中得到修复，但一如既往地感谢 Tom Lane。

E.1.3.1.3. Optimizer

Improve the ability of the planner to choose parameterized plans (Tom Lane)

A prepared statement is now parsed, analyzed, and rewritten, but not necessarily planned. When the prepared plan is executed with parameters, the planner might replan it for every constant, or it might execute a generic plan if its cost is close to that of a constant-specific plan.

参见 9.2 beta release notes和一个 quick note I wrote about this on lwn.net .在 mailing lists 上有很多关于处理运行速度比正常语句慢的准备好的/参数化语句的信息。 .