sql - QSqlQuery bindValue 慢

Question

我有这样的问题

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，下面的代码块大约需要 50 毫秒。

QSqlQuery newQuery(db);
newQuery.prepare(queryString);
newQuery.exec();

这正常吗？是什么造成了这种差异？

请注意，这些表的 FK 有 btree 索引。使用用VC2008SP1编译的Qt 4.7.4。数据库是 PostgreSQL。

Answer 1

回答我自己的问题(感谢 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