Skip to content

Commit 2220b86

Browse files
jef-njef-n
committedMay 19, 2019
postgres provider: performance improvements when loading layers
·
ltr-3_40final-3_8_0
1 parent 94cb854 commit 2220b86

File tree

5 files changed

+63
-57
lines changed

5 files changed

+63
-57
lines changed
 

‎src/core/qgsvectorlayer.cpp

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -773,15 +773,14 @@ QgsRectangle QgsVectorLayer::extent() const
773773
if ( !isSpatial() )
774774
return rect;
775775

776-
777776
if ( !mValidExtent && mLazyExtent && mDataProvider && !mDataProvider->hasMetadata() && mReadExtentFromXml && !mXmlExtent.isNull() )
778777
{
779778
mExtent = mXmlExtent;
780779
mValidExtent = true;
781780
mLazyExtent = false;
782781
}
783782

784-
if ( !mValidExtent && mLazyExtent && mDataProvider && mDataProvider->isValid() )
783+
if ( !mValidExtent && mLazyExtent && !mReadExtentFromXml && mDataProvider && mDataProvider->isValid() )
785784
{
786785
// get the extent
787786
QgsRectangle mbr = mDataProvider->extent();
@@ -812,7 +811,7 @@ QgsRectangle QgsVectorLayer::extent() const
812811

813812
// get the extent of the layer from the provider
814813
// but only when there are some features already
815-
if ( mDataProvider->featureCount() != 0 )
814+
if ( !mReadExtentFromXml && mDataProvider->featureCount() != 0 )
816815
{
817816
QgsRectangle r = mDataProvider->extent();
818817
rect.combineExtentWith( r );

‎src/providers/postgres/qgspostgresconn.cpp

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -149,8 +149,9 @@ QgsPostgresConn *QgsPostgresConn::connectDb( const QString &conninfo, bool reado
149149

150150
// This is called from may places where shared parameter cannot be forced to false (QgsVectorLayerExporter)
151151
// and which is run in a different thread (drag and drop in browser)
152-
if ( QApplication::instance()->thread() != QThread::currentThread() )
152+
if ( shared && QApplication::instance()->thread() != QThread::currentThread() )
153153
{
154+
QgsDebugMsg( QStringLiteral( "Connection cannot be shared between threads" ) );
154155
shared = false;
155156
}
156157

@@ -169,7 +170,6 @@ QgsPostgresConn *QgsPostgresConn::connectDb( const QString &conninfo, bool reado
169170
}
170171

171172
QgsPostgresConn *conn = new QgsPostgresConn( conninfo, readonly, shared, transaction );
172-
173173
if ( conn->mRef == 0 )
174174
{
175175
delete conn;
@@ -1933,3 +1933,31 @@ QString QgsPostgresConn::currentDatabase() const
19331933

19341934
return database;
19351935
}
1936+
1937+
const QgsPostgresConn::PGTypeInfo &QgsPostgresConn::type( int oid ) const
1938+
{
1939+
if ( mTypeMap.isEmpty() )
1940+
{
1941+
QMutexLocker locker( &mLock );
1942+
if ( mTypeMap.isEmpty() )
1943+
{
1944+
QString sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
1945+
QgsPostgresResult typeResult( PQexec( sql ) );
1946+
1947+
for ( int i = 0; i < typeResult.PQntuples(); ++i )
1948+
{
1949+
PGTypeInfo typeInfo =
1950+
{
1951+
/* typeName = */ typeResult.PQgetvalue( i, 1 ),
1952+
/* typeType = */ typeResult.PQgetvalue( i, 2 ),
1953+
/* typeElem = */ typeResult.PQgetvalue( i, 3 ),
1954+
/* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
1955+
};
1956+
mTypeMap.insert( typeResult.PQgetvalue( i, 0 ).toInt(), typeInfo );
1957+
}
1958+
QgsDebugMsg( "types retrieved" );
1959+
}
1960+
}
1961+
1962+
return mTypeMap[oid];
1963+
}

‎src/providers/postgres/qgspostgresconn.h

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -364,6 +364,16 @@ class QgsPostgresConn : public QObject
364364
void lock() { mLock.lock(); }
365365
void unlock() { mLock.unlock(); }
366366

367+
struct PGTypeInfo
368+
{
369+
QString typeName;
370+
QString typeType;
371+
QString typeElem;
372+
int typeLen;
373+
};
374+
375+
const PGTypeInfo &type( int oid ) const;
376+
367377
private:
368378
QgsPostgresConn( const QString &conninfo, bool readOnly, bool shared, bool transaction );
369379
~QgsPostgresConn() override;
@@ -436,6 +446,8 @@ class QgsPostgresConn : public QObject
436446

437447
bool mTransaction;
438448

449+
mutable QMap<int, PGTypeInfo> mTypeMap;
450+
439451
mutable QMutex mLock;
440452
};
441453

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 16 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -106,19 +106,6 @@ QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOpti
106106
mRequestedSrid = mUri.srid();
107107
mRequestedGeomType = mUri.wkbType();
108108

109-
if ( mUri.hasParam( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
110-
{
111-
112-
if ( mUri.param( QStringLiteral( "checkPrimaryKeyUnicity" ) ).compare( QLatin1String( "0" ) ) == 0 )
113-
{
114-
mCheckPrimaryKeyUnicity = false;
115-
}
116-
else
117-
{
118-
mCheckPrimaryKeyUnicity = true;
119-
}
120-
}
121-
122109
if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
123110
{
124111
mIsQuery = true;
@@ -143,12 +130,15 @@ QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOpti
143130
mUseEstimatedMetadata = mUri.useEstimatedMetadata();
144131
mSelectAtIdDisabled = mUri.selectAtIdDisabled();
145132

133+
mCheckPrimaryKeyUnicity = !mUri.hasParam( QStringLiteral( "checkPrimaryKeyUnicity" ) ) || mUri.param( QStringLiteral( "checkPrimaryKeyUnicity" ) ).compare( QLatin1String( "0" ) ) != 0;
134+
146135
QgsDebugMsg( QStringLiteral( "Connection info is %1" ).arg( mUri.connectionInfo( false ) ) );
147136
QgsDebugMsg( QStringLiteral( "Geometry column is: %1" ).arg( mGeometryColumn ) );
148137
QgsDebugMsg( QStringLiteral( "Schema is: %1" ).arg( mSchemaName ) );
149138
QgsDebugMsg( QStringLiteral( "Table name is: %1" ).arg( mTableName ) );
150139
QgsDebugMsg( QStringLiteral( "Query is: %1" ).arg( mQuery ) );
151140
QgsDebugMsg( QStringLiteral( "Where clause is: %1" ).arg( mSqlWhereClause ) );
141+
QgsDebugMsg( QStringLiteral( "Using estimated metadata: %1" ).arg( mUseEstimatedMetadata ) );
152142

153143
// no table/query passed, the provider could be used to get tables
154144
if ( mQuery.isEmpty() )
@@ -705,14 +695,6 @@ QString QgsPostgresProvider::endianString()
705695
}
706696

707697

708-
struct PGTypeInfo
709-
{
710-
QString typeName;
711-
QString typeType;
712-
QString typeElem;
713-
int typeLen;
714-
};
715-
716698
bool QgsPostgresProvider::loadFields()
717699
{
718700

@@ -744,24 +726,6 @@ bool QgsPostgresProvider::loadFields()
744726

745727
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
746728

747-
// Collect type info
748-
sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
749-
QgsPostgresResult typeResult( connectionRO()->PQexec( sql ) );
750-
751-
QMap<int, PGTypeInfo> typeMap;
752-
for ( int i = 0; i < typeResult.PQntuples(); ++i )
753-
{
754-
PGTypeInfo typeInfo =
755-
{
756-
/* typeName = */ typeResult.PQgetvalue( i, 1 ),
757-
/* typeType = */ typeResult.PQgetvalue( i, 2 ),
758-
/* typeElem = */ typeResult.PQgetvalue( i, 3 ),
759-
/* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
760-
};
761-
typeMap.insert( typeResult.PQgetvalue( i, 0 ).toInt(), typeInfo );
762-
}
763-
764-
765729
QMap<int, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap;
766730
QMap<int, QMap<int, int> > attTypeIdMap;
767731
QMap<int, QMap<int, bool> > notNullMap, uniqueMap;
@@ -840,12 +804,13 @@ bool QgsPostgresProvider::loadFields()
840804
int attnum = result.PQftablecol( i );
841805
int atttypid = attTypeIdMap[tableoid][attnum];
842806

843-
const PGTypeInfo &typeInfo = typeMap.value( fldtyp );
807+
const QgsPostgresConn::PGTypeInfo &typeInfo = connectionRO()->type( fldtyp );
844808
QString fieldTypeName = typeInfo.typeName;
845809
QString fieldTType = typeInfo.typeType;
846810
int fieldSize = typeInfo.typeLen;
847811

848-
bool isDomain = ( typeMap.value( atttypid ).typeType == QLatin1String( "d" ) );
812+
const QgsPostgresConn::PGTypeInfo &attTypeInfo = connectionRO()->type( atttypid );
813+
bool isDomain = ( attTypeInfo.typeType == QLatin1String( "d" ) );
849814

850815
QString formattedFieldType = fmtFieldTypeMap[tableoid][attnum];
851816
QString originalFormattedFieldType = formattedFieldType;
@@ -1792,7 +1757,8 @@ bool QgsPostgresProvider::parseDomainCheckConstraint( QStringList &enumValues, c
17921757
enumValues.clear();
17931758

17941759
//is it a domain type with a check constraint?
1795-
QString domainSql = QStringLiteral( "SELECT domain_name, domain_schema FROM information_schema.columns WHERE table_name=%1 AND column_name=%2" ).arg( quotedValue( mTableName ), quotedValue( attributeName ) );
1760+
QString domainSql = QStringLiteral( "SELECT domain_name,domain_schema FROM information_schema.columns WHERE table_schema=%1 AND table_name=%2 AND column_name=%3" )
1761+
.arg( quotedValue( mSchemaName ), quotedValue( mTableName ), quotedValue( attributeName ) );
17961762
QgsPostgresResult domainResult( connectionRO()->PQexec( domainSql ) );
17971763
if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 && !domainResult.PQgetvalue( 0, 0 ).isNull() )
17981764
{
@@ -3410,15 +3376,15 @@ bool QgsPostgresProvider::getGeometryDetails()
34103376
result = connectionRO()->PQexec( sql );
34113377
if ( tableoid > 0 && PGRES_TUPLES_OK == result.PQresultStatus() )
34123378
{
3413-
sql = QStringLiteral( "SELECT pg_namespace.nspname,pg_class.relname FROM pg_class,pg_namespace WHERE pg_class.relnamespace=pg_namespace.oid AND pg_class.oid=%1" ).arg( tableoid );
3379+
sql = QStringLiteral( "SELECT pg_namespace.nspname,pg_class.relname FROM pg_class JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid WHERE pg_class.oid=%1" ).arg( tableoid );
34143380
result = connectionRO()->PQexec( sql );
34153381

34163382
if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
34173383
{
34183384
schemaName = result.PQgetvalue( 0, 0 );
34193385
tableName = result.PQgetvalue( 0, 1 );
34203386

3421-
sql = QStringLiteral( "SELECT a.attname, t.typname FROM pg_attribute a, pg_type t WHERE a.attrelid=%1 AND a.attnum=%2 AND a.atttypid = t.oid" ).arg( tableoid ).arg( column );
3387+
sql = QStringLiteral( "SELECT a.attname, t.typname FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid WHERE a.attrelid=%1 AND a.attnum=%2" ).arg( tableoid ).arg( column );
34223388
result = connectionRO()->PQexec( sql );
34233389
if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
34243390
{
@@ -3567,11 +3533,12 @@ bool QgsPostgresProvider::getGeometryDetails()
35673533

35683534
if ( mSpatialColType == SctNone )
35693535
{
3570-
sql = QString( "SELECT t.typname FROM "
3571-
"pg_attribute a, pg_class c, pg_namespace n, pg_type t "
3572-
"WHERE a.attrelid=c.oid AND c.relnamespace=n.oid "
3573-
"AND a.atttypid=t.oid "
3574-
"AND n.nspname=%3 AND c.relname=%1 AND a.attname=%2" )
3536+
sql = QString( "SELECT t.typname"
3537+
" FROM pg_attribute a"
3538+
" JOIN pg_class c ON a.attrelid=c.oid"
3539+
" JOIN pg_namespace n ON c.relnamespace=n.oid"
3540+
" JOIN pg_type t ON a.atttypid=t.oid"
3541+
" WHERE n.nspname=%3 AND c.relname=%1 AND a.attname=%2" )
35753542
.arg( quotedValue( tableName ),
35763543
quotedValue( geomCol ),
35773544
quotedValue( schemaName ) );

‎src/server/services/wms/qgswmsgetcapabilities.cpp

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1062,10 +1062,10 @@ namespace QgsWms
10621062
if ( l->type() == QgsMapLayerType::VectorLayer )
10631063
{
10641064
QgsVectorLayer *vl = qobject_cast<QgsVectorLayer *>( l );
1065-
if ( vl && vl->featureCount() == 0 )
1065+
if ( vl && ( project->trustLayerMetadata() || vl->featureCount() == 0 ) )
10661066
{
1067-
// if there's no feature, use the wms extent defined in the
1068-
// project...
1067+
// if we trust the project or there's no feature, use the wms
1068+
// extent defined in the project...
10691069
extent = QgsServerProjectUtils::wmsExtent( *project );
10701070
if ( extent.isNull() )
10711071
{

1 commit comments

Comments
 (1)

nyalldawson commented on May 20, 2019

@nyalldawson
nyalldawson
on May 20, 2019
Collaborator

@jef-n I had to revert this in bf13d09 -- it broke the postgres unit tests

Please sign in to comment.