4 files changed, 22 insertions, 15 deletions
diff --git a/db/08to09.sql b/db/08to09.sql
index 1d37a759d..028fe56a4 100644
--- a/db/08to09.sql
+++ b/db/08to09.sql
@@ -51,3 +51,7 @@ alter table subscription
     add index subscription_subscriber_idx (subscriber,created),
     drop index subscription_subscribed_idx,
     add index subscription_subscribed_idx (subscribed,created);
+
+alter table notice
+    drop index notice_profile_id_idx,
+    add index notice_profile_id_idx (profile_id,created,id);
diff --git a/db/08to09_pg.sql b/db/08to09_pg.sql
index 004c77b36..d9b57fb84 100644
--- a/db/08to09_pg.sql
+++ b/db/08to09_pg.sql
@@ -40,20 +40,23 @@ create table user_role (
 );
 
 create table login_token (
-    user_id integer not null /* comment 'user owning this token'*/ references user (id),
+    user_id integer not null /* comment 'user owning this token'*/ references "user" (id),
     token char(32) not null /* comment 'token useable for logging in'*/,
     created timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date this record was created'*/,
     modified timestamp /* comment 'date this record was modified'*/,
 
-    constraint primary key (user_id)
+    primary key (user_id)
 );
 
-alter table fave
-    drop index fave_user_id_idx,
-    add index fave_user_id_idx using btree(user_id,modified);
+DROP index fave_user_id_idx;
+CREATE index fave_user_id_idx on fave (user_id,modified);
+
+DROP index subscription_subscriber_idx;
+CREATE index subscription_subscriber_idx ON subscription (subscriber,created);
+
+DROP index subscription_subscribed_idx;
+CREATE index subscription_subscribed_idx ON subscription (subscribed,created);
+
+DROP index notice_profile_id_idx;
+CREATE index notice_profile_id_idx ON notice (profile_id,created,id);
 
-alter table subscription
-    drop index subscription_subscriber_idx,
-    add index subscription_subscriber_idx using btree(subscriber,created),
-    drop index subscription_subscribed_idx,
-    add index subscription_subscribed_idx using btree(subscribed,created);
diff --git a/db/statusnet.sql b/db/statusnet.sql
index 2983c67cf..18abcdfdb 100644
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -130,7 +130,7 @@ create table notice (
     location_id integer comment 'location id if possible',
     location_ns integer comment 'namespace for location',
 
-    index notice_profile_id_idx (profile_id),
+    index notice_profile_id_idx (profile_id,created,id),
     index notice_conversation_idx (conversation),
     index notice_created_idx (created),
     index notice_replyto_idx (reply_to),
@@ -557,13 +557,13 @@ create table config (
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
-create table user_role (
+create table profile_role (
 
-    user_id integer not null comment 'user having the role' references user (id),
+    profile_id integer not null comment 'account having the role' references profile (id),
     role    varchar(32) not null comment 'string representing the role',
     created datetime not null comment 'date the role was granted',
 
-    constraint primary key (user_id, role)
+    constraint primary key (profile_id, role)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
diff --git a/db/statusnet_pg.sql b/db/statusnet_pg.sql
index 392a6a5f3..7b0e5313c 100644
--- a/db/statusnet_pg.sql
+++ b/db/statusnet_pg.sql
@@ -137,7 +137,7 @@ create table notice (
 
 /*    FULLTEXT(content) */
 );
-create index notice_profile_id_idx on notice using btree(profile_id);
+create index notice_profile_id_idx on notice using btree(profile_id,created,id);
 create index notice_created_idx on notice using btree(created);
 
 create table notice_source (