[Postgres provider] Speed up feature insertion when pkid column is not set (fixes #18976)

rouault · rouault · commit cc25727a1804 · 2018-06-01T22:31:09.000+02:00
When inserting features whose attribute column corresponding to the pkid
is empty, and tat this pkid column has a default value using a sequence,
then we can remove it from the INSERT statement completely, which save us
from doing a SELECT nextval(...) for each row. On the provided test case
of that ticket, on a debug build, this cuts down the insertion time from 5 minutes
to 1 minute 10s.
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -2029,15 +2029,41 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
       delim = ',';
     }
 
-    if ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 )
+    // Optimization: if we have a single primary key column whose default value
+    // is a sequence, and that none of the features have a value set for that
+    // column, then we can completely omit inserting it.
+    bool skipSinglePKField = false;
+
+    if ( ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 ) )
     {
-      Q_FOREACH ( int idx, mPrimaryKeyAttrs )
+      if ( mPrimaryKeyAttrs.size() == 1 &&
+           defaultValueClause( mPrimaryKeyAttrs[0] ).startsWith( "nextval(" ) )
       {
-        insert += delim + quotedIdentifier( field( idx ).name() );
-        values += delim + QStringLiteral( "$%1" ).arg( defaultValues.size() + offset );
-        delim = ',';
-        fieldId << idx;
-        defaultValues << defaultValueClause( idx );
+        bool foundNonNullPK = false;
+        int idx = mPrimaryKeyAttrs[0];
+        for ( int i = 0; i < flist.size(); i++ )
+        {
+          QgsAttributes attrs2 = flist[i].attributes();
+          QVariant v2 = attrs2.value( idx, QVariant( QVariant::Int ) );
+          if ( !v2.isNull() )
+          {
+            foundNonNullPK = true;
+            break;
+          }
+        }
+        skipSinglePKField = !foundNonNullPK;
+      }
+
+      if ( !skipSinglePKField )
+      {
+        Q_FOREACH ( int idx, mPrimaryKeyAttrs )
+        {
+          insert += delim + quotedIdentifier( field( idx ).name() );
+          values += delim + QStringLiteral( "$%1" ).arg( defaultValues.size() + offset );
+          delim = ',';
+          fieldId << idx;
+          defaultValues << defaultValueClause( idx );
+        }
       }
     }
 
@@ -2048,6 +2074,8 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
     for ( int idx = 0; idx < attributevec.count(); ++idx )
     {
       QVariant v = attributevec.value( idx, QVariant( QVariant::Int ) ); // default to NULL for missing attributes
+      if ( skipSinglePKField && idx == mPrimaryKeyAttrs[0] )
+        continue;
       if ( fieldId.contains( idx ) )
         continue;
 
