我有这样的疑问
select Count(1) as Count, pt.Name as TypeName, pt.ID as TypeID, pc.ID as CatID,
o.Name as OffName, o.ID as OffID, pc.Color as Color, s.ID, s.ActionType,
s.EndTime, pt.Size, pt.Price, pt.Unit, pt.OffID as ProdOffID
from sess s
inner join off o on o.id = s.offid
inner join act a on a.sessid = s.id
inner join prod p on p.tagid = a.prodid
inner join ProdType pt on pt.id = p.prodtypeid and pt.offid = p.Offid
left join prodcat pc on pc.id = pt.prodcatid and pc.offid = pt.offid
where s.offid = ? and s.acttype in (?, ?)
Group By pt.Name, pt.ID, pc.ID, o.Name,
o.ID, pc.Color, s.ID, s.ActType,
s.EndTime, pt.Size, pt.Price, pt.Unit, pt.OffID
如果我使用bindValue作为参数，下面的代码块将花费大量时间（大约2秒）
QSqlQuery newQuery(db);
newQuery.prepare(queryString);
for (int parameterIndex=0;parameterIndex<values.count();parameterIndex++) {
newQuery.bindValue(parameterIndex,values[parameterIndex]);
}
newQuery.exec();
但是如果我用值替换
?，如果不使用下面的bindValue代码块，大约需要50ms。QSqlQuery newQuery(db);
newQuery.prepare(queryString);
newQuery.exec();
这正常吗？这有什么区别？
注意，这些表的FK有btree索引。
使用用VC2008SP1编译的Qt4.7.4。数据库是PostgreSQL。
最佳答案
回答我自己的问题（感谢Mat）：
PostgreSQL根据值优化此查询的计划。因此，准备好的语句会阻止此类优化，并给出以下查询计划：
GroupAggregate (cost=581209.52..615986.02 rows=695530 width=72) (actual time=4067.645..4069.321 rows=101 loops=1)
-> Sort (cost=581209.52..582948.35 rows=695530 width=72) (actual time=4067.637..4067.719 rows=1832 loops=1)
Sort Key: pt.name, pt.id, pc.id, o.name, o.id, pc.color, s.id, s.actiontype, s.endtime, pt.size, pt.price, pt.unit, pt.officeid
Sort Method: quicksort Memory: 276kB
-> Hash Join (cost=49529.53..456659.15 rows=695530 width=72) (actual time=765.864..4047.298 rows=1832 loops=1)
Hash Cond: ((a.productid)::text = (p.tagid)::text)
-> Hash Join (cost=10640.07..391699.07 rows=555317 width=48) (actual time=41.884..3236.878 rows=2197 loops=1)
Hash Cond: (a.sessionid = s.id)
-> Seq Scan on action a (cost=0.00..280038.20 rows=15274820 width=29) (actual time=0.026..1586.065 rows=15274820 loops=1)
-> Hash (cost=10603.35..10603.35 rows=2938 width=23) (actual time=0.787..0.787 rows=116 loops=1)
-> Nested Loop (cost=208.16..10603.35 rows=2938 width=23) (actual time=0.234..0.747 rows=116 loops=1)
-> Seq Scan on office o (cost=0.00..4.26 rows=1 width=7) (actual time=0.012..0.019 rows=1 loops=1)
Filter: (id = $1)
-> Bitmap Heap Scan on session s (cost=208.16..10569.70 rows=2938 width=20) (actual time=0.216..0.701 rows=116 loops=1)
Recheck Cond: (s.officeid = $1)
Filter: (s.actiontype = ANY (ARRAY[$2, $3]))
-> Bitmap Index Scan on idx_session_officeid (cost=0.00..207.43 rows=11075 width=0) (actual time=0.103..0.103 rows=862 loops=1)
Index Cond: (s.officeid = $1)
-> Hash (cost=32726.06..32726.06 rows=244592 width=74) (actual time=707.589..707.589 rows=195238 loops=1)
-> Merge Join (cost=26994.35..32726.06 rows=244592 width=74) (actual time=383.882..595.784 rows=195238 loops=1)
Merge Cond: ((p.officeid = pt.officeid) AND (p.producttypeid = pt.id))
-> Sort (cost=26468.63..26956.84 rows=195284 width=33) (actual time=376.428..476.264 rows=195284 loops=1)
Sort Key: p.officeid, p.producttypeid
Sort Method: external merge Disk: 8776kB
-> Seq Scan on product p (cost=0.00..3966.84 rows=195284 width=33) (actual time=0.031..40.185 rows=195284 loops=1)
-> Sort (cost=525.72..536.77 rows=4421 width=49) (actual time=7.447..23.291 rows=199050 loops=1)
Sort Key: pt.officeid, pt.id
Sort Method: quicksort Memory: 618kB
-> Hash Left Join (cost=15.15..258.02 rows=4421 width=49) (actual time=0.194..3.094 rows=4421 loops=1)
Hash Cond: ((pt.productcategoryid = pc.id) AND (pt.officeid = pc.officeid))
-> Seq Scan on producttype pt (cost=0.00..112.21 rows=4421 width=41) (actual time=0.008..0.412 rows=4421 loops=1)
-> Hash (cost=8.46..8.46 rows=446 width=16) (actual time=0.175..0.175 rows=446 loops=1)
-> Seq Scan on productcategory pc (cost=0.00..8.46 rows=446 width=16) (actual time=0.005..0.075 rows=446 loops=1)
Total runtime: 4073.490 ms
但普通查询会以优化的方式改变查询计划：
HashAggregate (cost=14152.70..14164.53 rows=947 width=72) (actual time=38.517..38.555 rows=101 loops=1)
-> Hash Left Join (cost=247.52..14119.55 rows=947 width=72) (actual time=3.163..35.021 rows=1832 loops=1)
Hash Cond: ((pt.productcategoryid = pc.id) AND (pt.officeid = pc.officeid))
-> Hash Join (cost=232.37..14076.41 rows=947 width=64) (actual time=2.984..33.823 rows=1832 loops=1)
Hash Cond: ((p.producttypeid = pt.id) AND (p.officeid = pt.officeid))
-> Nested Loop (cost=53.85..13699.42 rows=756 width=31) (actual time=0.288..29.579 rows=1833 loops=1)
-> Nested Loop (cost=53.85..8111.65 rows=756 width=48) (actual time=0.222..2.292 rows=2197 loops=1)
-> Nested Loop (cost=53.85..6293.69 rows=4 width=23) (actual time=0.216..0.661 rows=116 loops=1)
-> Seq Scan on office o (cost=0.00..4.26 rows=1 width=7) (actual time=0.013..0.020 rows=1 loops=1)
Filter: (id = 1)
-> Bitmap Heap Scan on session s (cost=53.85..6289.39 rows=4 width=20) (actual time=0.196..0.613 rows=116 loops=1)
Recheck Cond: (s.officeid = 1)
Filter: (s.actiontype = ANY ('{0,2}'::integer[]))
-> Bitmap Index Scan on idx_session_officeid (cost=0.00..53.84 rows=2864 width=0) (actual time=0.099..0.099 rows=862 loops=1)
Index Cond: (s.officeid = 1)
-> Index Scan using idx_action_sessionid on action a (cost=0.00..452.13 rows=189 width=29) (actual time=0.004..0.010 rows=19 loops=116)
Index Cond: (a.sessionid = s.id)
-> Index Scan using product_pkey on product p (cost=0.00..7.38 rows=1 width=33) (actual time=0.011..0.011 rows=1 loops=2197)
Index Cond: ((p.tagid)::text = (a.productid)::text)
-> Hash (cost=112.21..112.21 rows=4421 width=41) (actual time=2.686..2.686 rows=4421 loops=1)
-> Seq Scan on producttype pt (cost=0.00..112.21 rows=4421 width=41) (actual time=0.003..1.169 rows=4421 loops=1)
-> Hash (cost=8.46..8.46 rows=446 width=16) (actual time=0.173..0.173 rows=446 loops=1)
-> Seq Scan on productcategory pc (cost=0.00..8.46 rows=446 width=16) (actual time=0.003..0.067 rows=446 loops=1)
Total runtime: 38.728 ms