postgresql – 使用参数而不是硬编码字符串时,Postgres查询非常
我遇到了这个Postgres问题,如果我在查询字符串上使用参数vs硬编码它的相同查询需要很长时间才能执行.列名是’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,$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',$3 = '1',$4 = '100',$5 = '0' duration: 71564.998 ms execute pdo_stmt_00000001: SELECT id,$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(现在测试版)中得到修复,尽管像往常一样感谢Tom Lane.
请参阅9.2 beta release notes和quick note I wrote about this on lwn.net.有很多关于处理准备/参数化语句的信息,这些语句在mailing lists上运行速度比正常情况慢. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |