A more efficient SQL query for uniqueness

[gjm]

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).

[jcs]

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.

[jef]

another candidate:

SELECT NOT EXISTS (SELECT %1 FROM %2.%3 GROUP BY %1 HAVING COUNT(*)>1) as isunique;