[prev in list] [next in list] [prev in thread] [next in thread] 

List:       kde-commits
Subject:    [akonadi] server/src: Speed up statistics queries from O(n^2) behavior by avoiding a double inner jo
From:       Till Adam <adam () kde ! org>
Date:       2012-08-27 12:33:01
Message-ID: 20120827123301.966F1A6094 () git ! kde ! org
[Download RAW message or body]

Git commit d59b46d482cc0980f9ebdf47748c8233caaba147 by Till Adam.
Committed on 27/08/2012 at 14:29.
Pushed by tilladam into branch 'master'.

Speed up statistics queries from O(n^2) behavior by avoiding a double inner join.

On Postgres 9.1 the query that uses two inner joins to limit the results
to collections with items with certain flags (like SEEN) is extremely
inefficient, for as yet unknown reasons. To work around this, we filter
manually by leveraging an in-memory cache of flag names to ids and using
those ids to filter.

Approved by vkrause.

M  +8    -4    server/src/handlerhelper.cpp

http://commits.kde.org/akonadi/d59b46d482cc0980f9ebdf47748c8233caaba147

diff --git a/server/src/handlerhelper.cpp b/server/src/handlerhelper.cpp
index 6767587..8631aa7 100644
--- a/server/src/handlerhelper.cpp
+++ b/server/src/handlerhelper.cpp
@@ -101,12 +101,16 @@ int HandlerHelper::itemWithFlagsCount(const \
Akonadi::Collection& col, const QStr  CountQueryBuilder qb( PimItem::tableName(), \
PimItem::idFullColumnName(), CountQueryBuilder::Distinct );  qb.addJoin( \
                QueryBuilder::InnerJoin, PimItemFlagRelation::tableName(),
               PimItem::idFullColumnName(), PimItemFlagRelation::leftFullColumnName() \
                );
-  qb.addJoin( QueryBuilder::InnerJoin, Flag::tableName(),
-              Flag::idFullColumnName(), PimItemFlagRelation::rightFullColumnName() \
);  qb.addValueCondition( PimItem::collectionIdFullColumnName(), Query::Equals, \
col.id() );  Query::Condition cond( Query::Or );
-  Q_FOREACH ( const QString &flag, flags )
-    cond.addValueCondition( Flag::nameFullColumnName(), Query::Equals, flag );
+  // We use the below instead of an inner join in the query above because postgres \
seems +  // to struggle to optimize the two inner joins, despite having indeces that \
should +  // facilitate that. This exploits the fact that the Flag::retrieveByName is \
fast because +  // it hits an in-memory cache.
+  Q_FOREACH ( const QString &flag, flags ) {
+    const Flag f = Flag::retrieveByName( flag );
+    cond.addValueCondition( PimItemFlagRelation::rightFullColumnName(), \
Query::Equals, f.id() ); +  }
   qb.addCondition( cond );
   if ( !qb.exec() )
     return -1;


[prev in list] [next in list] [prev in thread] [next in thread]