# HG changeset patch
# User Julien Cristau <julien.cristau@logilab.fr>
# Date 1384342770 -3600
# Node ID 722635e530a0bf50271779579d0ffccfdadbf0e9
# Parent  7da91456be2c61d69a14716b2feca8eee1d31f1d
[server] Add missing indices for undo support (closes #3259691)

The tables used by the 'undo' feature were missing some indices, which
among other things triggered repeated sequential scans when deleting old
transactions.

diff -r 7da91456be2c -r 722635e530a0 misc/migration/3.17.11_Any.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/misc/migration/3.17.11_Any.py	Wed Nov 13 12:39:30 2013 +0100
@@ -0,0 +1,7 @@
+for table, column in [
+        ('transactions', 'tx_time'),
+        ('tx_entity_actions', 'tx_uuid'),
+        ('tx_relation_actions', 'tx_uuid')]:
+    session.cnxset.source('system').create_index(session, table, column)
+
+commit()
diff -r 7da91456be2c -r 722635e530a0 server/sources/native.py
--- a/server/sources/native.py	Fri Dec 06 15:02:31 2013 +0100
+++ b/server/sources/native.py	Wed Nov 13 12:39:30 2013 +0100
@@ -1536,6 +1536,7 @@
   tx_time %s NOT NULL
 );;
 CREATE INDEX transactions_tx_user_idx ON transactions(tx_user);;
+CREATE INDEX transactions_tx_time_idx ON transactions(tx_time);;
 
 CREATE TABLE tx_entity_actions (
   tx_uuid CHAR(32) REFERENCES transactions(tx_uuid) ON DELETE CASCADE,
@@ -1550,6 +1551,7 @@
 CREATE INDEX tx_entity_actions_txa_public_idx ON tx_entity_actions(txa_public);;
 CREATE INDEX tx_entity_actions_eid_idx ON tx_entity_actions(eid);;
 CREATE INDEX tx_entity_actions_etype_idx ON tx_entity_actions(etype);;
+CREATE INDEX tx_entity_actions_tx_uuid_idx ON tx_entity_actions(tx_uuid);;
 
 CREATE TABLE tx_relation_actions (
   tx_uuid CHAR(32) REFERENCES transactions(tx_uuid) ON DELETE CASCADE,
@@ -1564,6 +1566,7 @@
 CREATE INDEX tx_relation_actions_txa_public_idx ON tx_relation_actions(txa_public);;
 CREATE INDEX tx_relation_actions_eid_from_idx ON tx_relation_actions(eid_from);;
 CREATE INDEX tx_relation_actions_eid_to_idx ON tx_relation_actions(eid_to);;
+CREATE INDEX tx_relation_actions_tx_uuid_idx ON tx_relation_actions(tx_uuid);;
 """ % (helper.sql_create_sequence('entities_id_seq').replace(';', ';;'),
        typemap['Datetime'], typemap['Datetime'], typemap['Datetime'],
        typemap['Boolean'], typemap['Bytes'], typemap['Boolean'])