Skip to content

Commit

Permalink
use other approach to avoid estimate_extents on not analyzed tables
Browse files Browse the repository at this point in the history 
git-svn-id: http://svn.osgeo.org/qgis/trunk@13221 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
jef committed Apr 1, 2010
1 parent 61c7b4c commit b4010ce
Show file tree
Hide file tree
Showing 2 changed files with 28 additions and 110 deletions.
118 changes: 23 additions & 95 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -234,28 +234,6 @@ QgsPostgresProvider::QgsPostgresProvider( QString const & uri )

// Kick off the long running threads

#ifdef POSTGRESQL_THREADS
QgsDebugMsg( "About to touch mExtentThread" );
mExtentThread.setConnInfo( mUri.connectionInfo );
mExtentThread.setTableName( mTableName );
mExtentThread.setSqlWhereClause( sqlWhereClause );
mExtentThread.setGeometryColumn( geometryColumn );
mExtentThread.setCallback( this );
QgsDebugMsg( "About to start mExtentThread" );
mExtentThread.start();
QgsDebugMsg( "Main thread just dispatched mExtentThread" );

QgsDebugMsg( "About to touch mCountThread" );
mCountThread.setConnInfo( mUri.connectionInfo );
mCountThread.setTableName( mTableName );
mCountThread.setSqlWhereClause( sqlWhereClause );
mCountThread.setGeometryColumn( geometryColumn );
mCountThread.setCallback( this );
QgsDebugMsg( "About to start mCountThread" );
mCountThread.start();
QgsDebugMsg( "Main thread just dispatched mCountThread" );
#endif

//fill type names into sets
mNativeTypes
// integer types
Expand Down Expand Up @@ -292,25 +270,6 @@ QgsPostgresProvider::QgsPostgresProvider( QString const & uri )

QgsPostgresProvider::~QgsPostgresProvider()
{
#ifdef POSTGRESQL_THREADS
QgsDebugMsg( "About to wait for mExtentThread" );

mExtentThread.wait();

QgsDebugMsg( "Finished waiting for mExtentThread" );

QgsDebugMsg( "About to wait for mCountThread" );

mCountThread.wait();

QgsDebugMsg( "Finished waiting for mCountThread" );

// Make sure all events from threads have been processed
// (otherwise they will get destroyed prematurely)
QApplication::sendPostedEvents( this, QGis::ProviderExtentCalcEvent );
QApplication::sendPostedEvents( this, QGis::ProviderCountCalcEvent );
#endif

disconnectDb();

QgsDebugMsg( "deconstructing." );
Expand Down Expand Up @@ -2730,68 +2689,38 @@ long QgsPostgresProvider::getFeatureCount()
return featuresCounted;
}

// TODO: use the estimateExtents procedure of PostGIS and PostgreSQL 8
// This tip thanks to #qgis irc nick "creeping"
void QgsPostgresProvider::calculateExtents()
{
#ifdef POSTGRESQL_THREADS
// get the approximate extent by retrieving the bounding box
// of the first few items with a geometry

QString sql = QString( "select box3d(%1) from %2 where " )
.arg( quotedIdentifier( geometryColumn ) )
.arg( mSchemaTableName );

if ( sqlWhereClause.length() > 0 )
{
sql += QString( "(%1) and " ).arg( sqlWhereClause );
}

sql += QString( "not IsEmpty(%1) limit 5" ).arg( quotedIdentifier( geometryColumn ) );

QgsDebugMsg( "Getting approximate extent using: '" + sql + "'" );

Result result = connectionRO->PQexec( sql );

// TODO: Guard against the result having no rows
for ( int i = 0; i < PQntuples( result ); i++ )
{
QString box3d = PQgetvalue( result, i, 0 );

if ( 0 == i )
{
// create the initial extent
layerExtent = QgsPostGisBox3d( box3d );
}
else
{
// extend the initial extent
QgsPostGisBox3d b = QgsPostGisBox3d( box3d );
layerExtent.combineExtentWith( &b );
}

QgsDebugMsg( QString( "After row %1, extent is %2" ).arg( i ).arg( layerExtent.toString() ) );
}

#else // non-postgresql threads version
QString sql;
Result result;
QString ext;

// get the extents
if (( mUseEstimatedMetadata || sqlWhereClause.isEmpty() ) && !connectionRO->hasNoExtentEstimate() )
if ( mUseEstimatedMetadata || sqlWhereClause.isEmpty() )
{
result = connectionRO->PQexec( QString( "select estimated_extent(%1,%2,%3)" )
.arg( quotedValue( mSchemaName ) )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( geometryColumn ) ) );
if ( PQresultStatus( result ) != PGRES_TUPLES_OK )
// do stats exists?
sql = QString( "SELECT COUNT(*) FROM pg_stats WHERE schemaname=%1 AND tablename=%2 AND attname=%3" )
.arg( quotedValue( mSchemaName ) )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( geometryColumn ) );
result = connectionRO->PQexec( sql );
if ( PQresultStatus( result ) == PGRES_TUPLES_OK && PQntuples( result ) == 1 )
{
connectionRO->PQexecNR( "ROLLBACK" );
connectionRO->setNoExtentEstimate();
if ( QString::fromUtf8( PQgetvalue( result, 0, 0 ) ).toInt() > 0 )
{
sql = QString( "select estimated_extent(%1,%2,%3)" )
.arg( quotedValue( mSchemaName ) )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( geometryColumn ) );
result = connectionRO->PQexec( sql );
if ( PQresultStatus( result ) == PGRES_TUPLES_OK && PQntuples( result ) == 1 )
ext = PQgetvalue( result, 0, 0 );
}
}
else
{
QgsDebugMsg( QString( "no column statistics for %1.%2.%3" ).arg( mSchemaName ).arg( mTableName ).arg( geometryColumn ) );
}
else if ( PQntuples( result ) == 1 )
ext = PQgetvalue( result, 0, 0 );
}

if ( ext.isEmpty() )
Expand All @@ -2810,7 +2739,7 @@ void QgsPostgresProvider::calculateExtents()
ext = PQgetvalue( result, 0, 0 );
}

QgsDebugMsg( "Getting extents using schema.table: " + sql );
QgsDebugMsg( "Got extents using: " + sql );

QRegExp rx( "\\((.+) (.+),(.+) (.+)\\)" );
if ( ext.contains( rx ) )
Expand All @@ -2826,7 +2755,6 @@ void QgsPostgresProvider::calculateExtents()
{
QgsDebugMsg( "extents query failed" );
}
#endif

QgsDebugMsg( "Set extents to: " + layerExtent.toString() );
}
Expand Down
20 changes: 5 additions & 15 deletions src/providers/postgres/qgspostgresprovider.h
View file
Expand Up @@ -584,12 +584,11 @@ class QgsPostgresProvider : public QgsVectorDataProvider
class Conn
{
public:
Conn( PGconn *connection ) :
ref( 1 ),
openCursors( 0 ),
conn( connection ),
gotPostgisVersion( false ),
mHasNoExtentEstimate( false )
Conn( PGconn *connection )
: ref( 1 )
, openCursors( 0 )
, conn( connection )
, gotPostgisVersion( false )
{
}

Expand All @@ -614,12 +613,6 @@ class QgsPostgresProvider : public QgsVectorDataProvider
//! PostgreSQL version
int pgVersion() { return postgresqlVersion; }

//! has PostGIS no extent estimate?
bool hasNoExtentEstimate() { return mHasNoExtentEstimate; }

//! PostGIS does not have a extent estimate
void setNoExtentEstimate( bool flag = true ) { mHasNoExtentEstimate = flag; }

//! run a query and free result buffer
bool PQexecNR( QString query );

Expand Down Expand Up @@ -678,9 +671,6 @@ class QgsPostgresProvider : public QgsVectorDataProvider
//! encode wkb in hex
bool mUseWkbHex;

//! PostGIS doesn't have extent estimates
bool mHasNoExtentEstimate;

static QMap<QString, Conn *> connectionsRW;
static QMap<QString, Conn *> connectionsRO;
static QMap<QString, QString> passwordCache;
Expand Down

0 comments on commit b4010ce

Please sign in to comment.