开发者

PostgreSQL and QSqlQuery.bindValue() are slow

开发者 https://www.devze.com 2023-03-20 10:59 出处:网络
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.nam

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

精彩评论

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