gistfile1.txt

BEGIN;
drop table if exists users cascade;
drop table if exists contacts;

create table users (userid int not null primary key);
insert into users (userid) SELECT i FROM generate_series(1, 100000) AS i;

-- Table: contacts

-- DROP TABLE contacts;

CREATE TABLE contacts
(
  userid1 integer NOT NULL,
  userid2 integer NOT NULL,
  style integer NOT NULL,
  lastchange timestamp with time zone,
  CONSTRAINT contacts_pkey PRIMARY KEY (userid1 , userid2 ),
  CONSTRAINT contacts_userid1_fkey FOREIGN KEY (userid1)
      REFERENCES users (userid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT contacts_userid2_fkey FOREIGN KEY (userid2)
      REFERENCES users (userid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
DROP ROLE IF EXISTS testuser;
CREATE ROLE testuser;
ALTER TABLE contacts
  OWNER TO testuser;

-- Index: idx_contacts_user1

-- DROP INDEX idx_contacts_user1;

CREATE INDEX idx_contacts_user1
  ON contacts
  USING btree
  (userid1  DESC);
ALTER TABLE contacts CLUSTER ON idx_contacts_user1;

-- Index: idx_contacts_user2

-- DROP INDEX idx_contacts_user2;

CREATE INDEX idx_contacts_user2
  ON contacts
  USING btree
  (userid2  DESC);
COMMIT;