使用参数而不是硬编码字符串时，Postgres查询非常慢 [英] Postgres query is very slow when using a parameter instead of an hardcoded string

问题描述

我正在遇到这样一个Postgres问题，如果我使用参数vs hardcoding其在查询字符串上的值，同样的查询需要很长时间才能执行。列名称为media_type，它是一个VARCHAR（20）。我从PHP运行这些查询，使用Symfony2和Doctrine2 ORM，而有问题的表有大约1.000.000条记录。

I am running into this Postgres issue where the same query takes a long time to execute if I use a parameter vs hardcoding its value on the query string. The column name is 'media_type' and it's a VARCHAR(20). I am running these queries from PHP, using Symfony2 and Doctrine2 ORM and the table in question has about 1.000.000 records.

我的查询有问题吗？可以是Postgres配置问题吗？

Do I have a problem on my query? Can it be a Postgres configuration problem?

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（SLOWER）的参数

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

提前感谢

推荐答案

这是一个长期的疣PostgreSQL在历史上需要一些有趣的计划员调整来解决。它固定在PostgreSQL 9.2（现在测试版），尽管如往常一样，汤姆巷。

This is a bit of a long-standing wart in PostgreSQL that's historically required some interesting planner tuning to work around. It's fixed in PostgreSQL 9.2 (in beta now) though thanks, as usual, to Tom Lane.

E.1.3.1.3。优化器

E.1.3.1.3. Optimizer

提高计划者选择参数化计划的能力（Tom
Lane）

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

A准备的声明现在已经被解析，分析和重写，但不是必须计划的
。当使用
参数执行准备的计划时，计划程序可能会为每个常量复制它，否则
可能执行一个通用计划，如果其成本接近
常量特定计划的成本。

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测试版发行说明和一个我在lwn.net上写到的快速笔记。有关在邮件列表上处理比正常语言运行速度较慢的准备/参数化语句的信息很多。

See the 9.2 beta release notes and a quick note I wrote about this on lwn.net. There's lots of info about handling prepared / parameterised statements running slower than normal ones on the mailing lists.

这篇关于使用参数而不是硬编码字符串时，Postgres查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！