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
精彩评论