[Checkins] SVN: relstorage/trunk/ More pack optimizations based on better use of indexes.

[Shane Hathaway]

Log message for revision 85056:
  More pack optimizations based on better use of indexes.
  The pack stress test seems to behave pretty
  well with these optimizations and fsync enabled.
  
  So-called relational databases sure are finicky.
  
  

Changed:
  U   relstorage/trunk/notes/migrate-1.0.1.txt
  U   relstorage/trunk/relstorage/adapters/common.py
  U   relstorage/trunk/relstorage/adapters/postgresql.py

-=-
Modified: relstorage/trunk/notes/migrate-1.0.1.txt
===================================================================
--- relstorage/trunk/notes/migrate-1.0.1.txt	2008-04-01 05:30:45 UTC (rev 85055)
+++ relstorage/trunk/notes/migrate-1.0.1.txt	2008-04-01 07:00:46 UTC (rev 85056)
@@ -1,9 +1,17 @@
 
 Migrating from version 1.0.1
 
-Create a new index on the object_state table.  The new pack code will
-probably crawl if this index is missing.  This statement works in all 3
-supported databases:
+Create a new index on the object_state table.  The new iterative pack code
+will operate slowly without this index.  The following statement works in
+all 3 supported databases:
 
-  CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
+    CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
 
+Also, for PostgreSQL only, apply this pack optimization:
+
+    DROP INDEX pack_object_keep_zoid;
+    CREATE INDEX pack_object_keep_false ON pack_object (zoid)
+        WHERE keep = false;
+    CREATE INDEX pack_object_keep_true ON pack_object (zoid, keep_tid)
+        WHERE keep = true;
+

Modified: relstorage/trunk/relstorage/adapters/common.py
===================================================================
--- relstorage/trunk/relstorage/adapters/common.py	2008-04-01 05:30:45 UTC (rev 85055)
+++ relstorage/trunk/relstorage/adapters/common.py	2008-04-01 07:00:46 UTC (rev 85056)
@@ -742,38 +742,34 @@
         """Pack one transaction.  Requires populated pack tables."""
         log.debug("pack: transaction %d: packing", tid)
         deleted = 0
-        for table in ('object_ref', 'current_object', 'object_state'):
+        for _table in ('object_ref', 'current_object', 'object_state'):
             # Remove objects that are in pack_object and have keep
             # set to false.
             stmt = """
-            DELETE FROM %s
-            WHERE tid = %%(tid)s
+            DELETE FROM _table
+            WHERE tid = %(tid)s
                 AND zoid IN (
                     SELECT zoid
                     FROM pack_object
-                    WHERE keep = %%(FALSE)s
+                    WHERE keep = %(FALSE)s
                 )
-            """ % table
+            """.replace('_table', _table)
             self._run_script_stmt(cursor, stmt, {'tid': tid})
             deleted += cursor.rowcount
 
-            if table != 'current_object':
+            if _table != 'current_object':
                 # Cut the history of objects in pack_object that
                 # have keep set to true.
                 stmt = """
-                DELETE FROM %s
-                WHERE tid = %%(tid)s
+                DELETE FROM _table
+                WHERE tid = %(tid)s
                     AND zoid IN (
                         SELECT zoid
                         FROM pack_object
-                        WHERE keep = %%(TRUE)s
+                        WHERE keep = %(TRUE)s
+                            AND keep_tid != %(tid)s
                     )
-                    AND tid < (
-                        SELECT keep_tid
-                        FROM pack_object
-                        WHERE zoid = %s.zoid
-                    )
-                """ % (table, table)
+                """.replace('_table', _table)
                 self._run_script_stmt(cursor, stmt, {'tid': tid})
                 deleted += cursor.rowcount
 
@@ -795,6 +791,7 @@
             stmt = """
             UPDATE transaction SET packed = %(TRUE)s
             WHERE tid = %(tid)s
+                AND packed = %(FALSE)s
             """
             self._run_script_stmt(cursor, stmt, {'tid': tid})
 

Modified: relstorage/trunk/relstorage/adapters/postgresql.py
===================================================================
--- relstorage/trunk/relstorage/adapters/postgresql.py	2008-04-01 05:30:45 UTC (rev 85055)
+++ relstorage/trunk/relstorage/adapters/postgresql.py	2008-04-01 07:00:46 UTC (rev 85056)
@@ -118,7 +118,10 @@
             keep        BOOLEAN NOT NULL,
             keep_tid    BIGINT
         );
-        CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);
+        CREATE INDEX pack_object_keep_false ON pack_object (zoid)
+            WHERE keep = false;
+        CREATE INDEX pack_object_keep_true ON pack_object (zoid, keep_tid)
+            WHERE keep = true;
         """
         cursor.execute(stmt)