Feature request #1555
A more efficient SQL query for uniqueness
|Pull Request or Patch supplied:||No||Resolution:|
|Easy fix?:||No||Copied to github as #:||11615|
The postgres provider, at times, runs a query on a table to see if a particular column contains unique values. This is done as part of choosing a suitable column to use as an index for that table. The SQL that does this uniqueness check is in the uniqueData() function in the qgspostgresprovider.cpp file. The SQL is:
select count(distinct %1)=count(%1) from %2.%3
where %1 is the column in question, %2 the schema name and %3 the table name.
This counts the number of rows in that row almost twice. A potentially more efficient way to achieve the same outcome is with an SQL like this:
select count() from (select %1 from %2.%3 group by %1 having count() > 1 limit 1) as foo;
This would return 0 or 1, depending if there were unique (or not) data in row %1.
This needs a little bit of testing first to check that it does reduce the query time (I don't have the time at the moment).
#1 Updated by jcs - over 10 years ago
It's still not clear to me that trying to guess the index column is the best approach. I contend that the user will always know more about the database than the application, so let the user say what column to use. I have a patch for this feature, see ticket #1535 if interested.