diff options
| author | Holger Weiss <holger@zedat.fu-berlin.de> | 2018-02-24 00:50:20 +0100 |
|---|---|---|
| committer | Holger Weiss <holger@zedat.fu-berlin.de> | 2018-02-24 00:50:20 +0100 |
| commit | f7566bd00e687df60fd470b9fdfc5ce6b04f0c7c (patch) | |
| tree | 699db516e8fbdcdf11d998fe57e9aaa3ced0a078 /sql/pg.new.sql | |
| parent | Merge pull request #2297 from af8a524db1/move_make_rand_string (diff) | |
sql/*: Add username to peer indexes
The username is available for all MAM queries in question, and adding it
to the indexes can improve the lookup performance significantly.
Diffstat (limited to 'sql/pg.new.sql')
| -rw-r--r-- | sql/pg.new.sql | 13 |
1 files changed, 6 insertions, 7 deletions
diff --git a/sql/pg.new.sql b/sql/pg.new.sql index 3d613a37b..66d934236 100644 --- a/sql/pg.new.sql +++ b/sql/pg.new.sql @@ -61,15 +61,14 @@ -- ALTER TABLE spool ALTER COLUMN server_host DROP DEFAULT; -- ALTER TABLE archive ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; --- DROP INDEX i_username; -- DROP INDEX i_username_timestamp; +-- DROP INDEX i_username_peer; +-- DROP INDEX i_username_bare_peer; -- DROP INDEX i_timestamp; --- DROP INDEX i_peer; --- DROP INDEX i_bare_peer; -- CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp); +-- CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer); +-- CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer); -- CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp); --- CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer); --- CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer); -- ALTER TABLE archive ALTER COLUMN server_host DROP DEFAULT; -- ALTER TABLE archive_prefs ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; @@ -265,9 +264,9 @@ CREATE TABLE archive ( ); CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp); +CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer); +CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer); CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp); -CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer); -CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer); CREATE TABLE archive_prefs ( username text NOT NULL, |