Feature request #1555
A more efficient SQL query for uniqueness
|Assigned to:||nobody -||% Done:||
|Target version:||Future Release - Nice to have|
|Platform version:||Pull Request or Patch supplied:|
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).
Updated by jcs - about 8 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.
Updated by Jürgen Fischer about 8 years ago
SELECT NOT EXISTS (SELECT %1 FROM %2.%3 GROUP BY %1 HAVING COUNT(*)>1) as isunique;
Updated by Giovanni Manghi over 5 years ago
- Target version changed from Version 1.7.0 to Version 1.7.4
Updated by Giovanni Manghi almost 5 years ago
- Target version changed from Version 1.7.4 to Version 2.0.0
Also available in: Atom