[or-cvs] [metrics-db/master] Add indexes for searching relays and update primary key definition.

[karsten@xxxxxxxxxxxxxx]

Author: Karsten Loesing <karsten.loesing@xxxxxxx>
Date: Mon, 20 Sep 2010 18:47:26 +0200
Subject: Add indexes for searching relays and update primary key definition.
Commit: eff6d5908c0838c49ec2552250be1c480b5908b3

---
 db/tordir.sql |    9 ++++++++-
 1 files changed, 8 insertions(+), 1 deletions(-)

diff --git a/db/tordir.sql b/db/tordir.sql
index 353bcef..5861a24 100644
--- a/db/tordir.sql
+++ b/db/tordir.sql
@@ -62,9 +62,16 @@ CREATE TABLE statusentry (
     bandwidth BIGINT,
     ports TEXT,
     rawdesc BYTEA NOT NULL,
-    CONSTRAINT statusentry_pkey PRIMARY KEY (validafter, descriptor)
+    CONSTRAINT statusentry_pkey PRIMARY KEY (validafter, fingerprint)
 );
 
+-- Create the various indexes we need for searching relays
+CREATE INDEX statusentry_address ON statusentry (address);
+CREATE INDEX statusentry_descriptor ON statusentry (descriptor);
+CREATE INDEX statusentry_fingerprint ON statusentry (fingerprint);
+CREATE INDEX statusentry_nickname_lower ON statusentry (LOWER(nickname));
+CREATE INDEX statusentry_validafter ON statusentry (validafter);
+
 -- TABLE network_size
 -- TODO Instead of having a separate column for each flag we could add
 -- two columns 'flag' and 'relays' to add more flags more easily.
-- 
1.7.1