Feature request #1555

A more efficient SQL query for uniqueness

Added by gjm - over 8 years ago. Updated 5 months ago.

Status:Open
Priority:Low
Assignee:nobody -
Category:Data Provider
Pull Request or Patch supplied:No Resolution:
Easy fix?:No

Description

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

History

#1 Updated by jcs - over 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.

#2 Updated by Jürgen Fischer over 8 years ago

another candidate:

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

#3 Updated by Giovanni Manghi almost 6 years ago

  • Target version changed from Version 1.7.0 to Version 1.7.4

#4 Updated by Giovanni Manghi over 5 years ago

  • Target version changed from Version 1.7.4 to Version 2.0.0

#5 Updated by Pirmin Kalberer almost 5 years ago

  • Target version changed from Version 2.0.0 to Future Release - Nice to have

#6 Updated by Giovanni Manghi 5 months ago

  • Easy fix? set to No
  • Pull Request or Patch supplied set to No

Also available in: Atom PDF