Skip to content

Commit

Permalink
postgres provider: performance improvements when loading layers
Browse files Browse the repository at this point in the history
  • Loading branch information
@jef-n
jef-n committed May 19, 2019
1 parent 94cb854 commit 2220b86
Show file tree
Hide file tree
Showing 5 changed files with 63 additions and 57 deletions.
5 changes: 2 additions & 3 deletions src/core/qgsvectorlayer.cpp
View file
Expand Up @@ -773,15 +773,14 @@ QgsRectangle QgsVectorLayer::extent() const
if ( !isSpatial() )
return rect;


if ( !mValidExtent && mLazyExtent && mDataProvider && !mDataProvider->hasMetadata() && mReadExtentFromXml && !mXmlExtent.isNull() )
{
mExtent = mXmlExtent;
mValidExtent = true;
mLazyExtent = false;
}

if ( !mValidExtent && mLazyExtent && mDataProvider && mDataProvider->isValid() )
if ( !mValidExtent && mLazyExtent && !mReadExtentFromXml && mDataProvider && mDataProvider->isValid() )
{
// get the extent
QgsRectangle mbr = mDataProvider->extent();
Expand Down Expand Up @@ -812,7 +811,7 @@ QgsRectangle QgsVectorLayer::extent() const

// get the extent of the layer from the provider
// but only when there are some features already
if ( mDataProvider->featureCount() != 0 )
if ( !mReadExtentFromXml && mDataProvider->featureCount() != 0 )
{
QgsRectangle r = mDataProvider->extent();
rect.combineExtentWith( r );
Expand Down
32 changes: 30 additions & 2 deletions src/providers/postgres/qgspostgresconn.cpp
View file
Expand Up @@ -149,8 +149,9 @@ QgsPostgresConn *QgsPostgresConn::connectDb( const QString &conninfo, bool reado

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

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

QgsPostgresConn *conn = new QgsPostgresConn( conninfo, readonly, shared, transaction );

if ( conn->mRef == 0 )
{
delete conn;
Expand Down Expand Up @@ -1933,3 +1933,31 @@ QString QgsPostgresConn::currentDatabase() const

return database;
}

const QgsPostgresConn::PGTypeInfo &QgsPostgresConn::type( int oid ) const
{
if ( mTypeMap.isEmpty() )
{
QMutexLocker locker( &mLock );
if ( mTypeMap.isEmpty() )
{
QString sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
QgsPostgresResult typeResult( PQexec( sql ) );

for ( int i = 0; i < typeResult.PQntuples(); ++i )
{
PGTypeInfo typeInfo =
{
/* typeName = */ typeResult.PQgetvalue( i, 1 ),
/* typeType = */ typeResult.PQgetvalue( i, 2 ),
/* typeElem = */ typeResult.PQgetvalue( i, 3 ),
/* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
};
mTypeMap.insert( typeResult.PQgetvalue( i, 0 ).toInt(), typeInfo );
}
QgsDebugMsg( "types retrieved" );
}
}

return mTypeMap[oid];
}
12 changes: 12 additions & 0 deletions src/providers/postgres/qgspostgresconn.h
View file
Expand Up @@ -364,6 +364,16 @@ class QgsPostgresConn : public QObject
void lock() { mLock.lock(); }
void unlock() { mLock.unlock(); }

struct PGTypeInfo
{
QString typeName;
QString typeType;
QString typeElem;
int typeLen;
};

const PGTypeInfo &type( int oid ) const;

private:
QgsPostgresConn( const QString &conninfo, bool readOnly, bool shared, bool transaction );
~QgsPostgresConn() override;
Expand Down Expand Up @@ -436,6 +446,8 @@ class QgsPostgresConn : public QObject

bool mTransaction;

mutable QMap<int, PGTypeInfo> mTypeMap;

mutable QMutex mLock;
};

Expand Down
65 changes: 16 additions & 49 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -106,19 +106,6 @@ QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOpti
mRequestedSrid = mUri.srid();
mRequestedGeomType = mUri.wkbType();

if ( mUri.hasParam( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
{

if ( mUri.param( QStringLiteral( "checkPrimaryKeyUnicity" ) ).compare( QLatin1String( "0" ) ) == 0 )
{
mCheckPrimaryKeyUnicity = false;
}
else
{
mCheckPrimaryKeyUnicity = true;
}
}

if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
{
mIsQuery = true;
Expand All @@ -143,12 +130,15 @@ QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOpti
mUseEstimatedMetadata = mUri.useEstimatedMetadata();
mSelectAtIdDisabled = mUri.selectAtIdDisabled();

mCheckPrimaryKeyUnicity = !mUri.hasParam( QStringLiteral( "checkPrimaryKeyUnicity" ) ) || mUri.param( QStringLiteral( "checkPrimaryKeyUnicity" ) ).compare( QLatin1String( "0" ) ) != 0;

QgsDebugMsg( QStringLiteral( "Connection info is %1" ).arg( mUri.connectionInfo( false ) ) );
QgsDebugMsg( QStringLiteral( "Geometry column is: %1" ).arg( mGeometryColumn ) );
QgsDebugMsg( QStringLiteral( "Schema is: %1" ).arg( mSchemaName ) );
QgsDebugMsg( QStringLiteral( "Table name is: %1" ).arg( mTableName ) );
QgsDebugMsg( QStringLiteral( "Query is: %1" ).arg( mQuery ) );
QgsDebugMsg( QStringLiteral( "Where clause is: %1" ).arg( mSqlWhereClause ) );
QgsDebugMsg( QStringLiteral( "Using estimated metadata: %1" ).arg( mUseEstimatedMetadata ) );

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


struct PGTypeInfo
{
QString typeName;
QString typeType;
QString typeElem;
int typeLen;
};

bool QgsPostgresProvider::loadFields()
{

Expand Down Expand Up @@ -744,24 +726,6 @@ bool QgsPostgresProvider::loadFields()

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

// Collect type info
sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
QgsPostgresResult typeResult( connectionRO()->PQexec( sql ) );

QMap<int, PGTypeInfo> typeMap;
for ( int i = 0; i < typeResult.PQntuples(); ++i )
{
PGTypeInfo typeInfo =
{
/* typeName = */ typeResult.PQgetvalue( i, 1 ),
/* typeType = */ typeResult.PQgetvalue( i, 2 ),
/* typeElem = */ typeResult.PQgetvalue( i, 3 ),
/* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
};
typeMap.insert( typeResult.PQgetvalue( i, 0 ).toInt(), typeInfo );
}


QMap<int, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap;
QMap<int, QMap<int, int> > attTypeIdMap;
QMap<int, QMap<int, bool> > notNullMap, uniqueMap;
Expand Down Expand Up @@ -840,12 +804,13 @@ bool QgsPostgresProvider::loadFields()
int attnum = result.PQftablecol( i );
int atttypid = attTypeIdMap[tableoid][attnum];

const PGTypeInfo &typeInfo = typeMap.value( fldtyp );
const QgsPostgresConn::PGTypeInfo &typeInfo = connectionRO()->type( fldtyp );
QString fieldTypeName = typeInfo.typeName;
QString fieldTType = typeInfo.typeType;
int fieldSize = typeInfo.typeLen;

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

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

//is it a domain type with a check constraint?
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 ) );
QString domainSql = QStringLiteral( "SELECT domain_name,domain_schema FROM information_schema.columns WHERE table_schema=%1 AND table_name=%2 AND column_name=%3" )
.arg( quotedValue( mSchemaName ), quotedValue( mTableName ), quotedValue( attributeName ) );
QgsPostgresResult domainResult( connectionRO()->PQexec( domainSql ) );
if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 && !domainResult.PQgetvalue( 0, 0 ).isNull() )
{
Expand Down Expand Up @@ -3410,15 +3376,15 @@ bool QgsPostgresProvider::getGeometryDetails()
result = connectionRO()->PQexec( sql );
if ( tableoid > 0 && PGRES_TUPLES_OK == result.PQresultStatus() )
{
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 );
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 );
result = connectionRO()->PQexec( sql );

if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
{
schemaName = result.PQgetvalue( 0, 0 );
tableName = result.PQgetvalue( 0, 1 );

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 );
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 );
result = connectionRO()->PQexec( sql );
if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
{
Expand Down Expand Up @@ -3567,11 +3533,12 @@ bool QgsPostgresProvider::getGeometryDetails()

if ( mSpatialColType == SctNone )
{
sql = QString( "SELECT t.typname FROM "
"pg_attribute a, pg_class c, pg_namespace n, pg_type t "
"WHERE a.attrelid=c.oid AND c.relnamespace=n.oid "
"AND a.atttypid=t.oid "
"AND n.nspname=%3 AND c.relname=%1 AND a.attname=%2" )
sql = QString( "SELECT t.typname"
" FROM pg_attribute a"
" JOIN pg_class c ON a.attrelid=c.oid"
" JOIN pg_namespace n ON c.relnamespace=n.oid"
" JOIN pg_type t ON a.atttypid=t.oid"
" WHERE n.nspname=%3 AND c.relname=%1 AND a.attname=%2" )
.arg( quotedValue( tableName ),
quotedValue( geomCol ),
quotedValue( schemaName ) );
Expand Down
6 changes: 3 additions & 3 deletions src/server/services/wms/qgswmsgetcapabilities.cpp
View file
Expand Up @@ -1062,10 +1062,10 @@ namespace QgsWms
if ( l->type() == QgsMapLayerType::VectorLayer )
{
QgsVectorLayer *vl = qobject_cast<QgsVectorLayer *>( l );
if ( vl && vl->featureCount() == 0 )
if ( vl && ( project->trustLayerMetadata() || vl->featureCount() == 0 ) )
{
// if there's no feature, use the wms extent defined in the
// project...
// if we trust the project or there's no feature, use the wms
// extent defined in the project...
extent = QgsServerProjectUtils::wmsExtent( *project );
if ( extent.isNull() )
{
Expand Down

1 comment on commit 2220b86

@nyalldawson
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

Please sign in to comment.