开发者

PostgreSQL and QSqlQuery.bindValue() are slow

I have a PostgreSQL database, a table of 1,5M records in it and code like this:

    QString sql = "SELECT p.id, initcap(p.lname) lname, initcap(p.fname) fname, initcap(p.mname) mname, p.birthday, c.name as cname, p.enp "
    "FROM oms.persons p " 
    "LEFT JOIN ref.countries c ON (p.citizenship = c.code)开发者_开发技巧 "
    "WHERE p.lname LIKE :LNAME " 
    "ORDER BY p.lname, p.fname, p.mname LIMIT 100";

    QSqlQuery q;
    q.prepare(sql);
    q.bindValue(":LNAME", "TEST%");
    q.exec();

It takes ~1200 msec, but if I exec this query without bindValue() function and replace :LNAME to 'TEST%' it takes only ~30 msec. What should I do to fix this strange QSqlQuery behavior?

Specs: Qt 4.7.3 Windows PostgreSQL 8.4.8 on Debian Linux


The answer is simple: "The LIKE index optimization depends on seeing a constant LIKE pattern at plan time - otherwise the planner doesn't know what indexscan parameters to generate."

http://archives.postgresql.org/pgsql-hackers/2006-05/msg00969.php

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜