how to make this database / query faster

[mark <markkicks@xxxxxxxxx>]

Hi
I use postgres v 8.3 on a dual quad core, intel xeon e5405@xxxxxxx, fedora core 8 x86_64, and 32GB RAM

settings i changed on postgresql.conf:

shared_buffers = 1000MB                 # min 128kB or max_connections*16kB

effective_cache_size = 4000MB


I have a user table structure is attached and I have around 2 million rows and adding like 10k-30k rows everyday..
id is the primary key, and i have an index session_key

i iterate through the users table like this

select * from users where session_key is not Null order by id offset OFFSET limit 300


i want to go through the whole table... it gets really slow like greater than 5 minutes when the OFFSET is over 500,000..

what is the best way to iterate through the whole table? should i increase the  limit?
thanks a lot!
CREATE TABLE users
(
  id serial NOT NULL,
  username text,
  first_name text,
  last_name text,
  email text,
  "password" text,
  last_login timestamp without time zone,
  profilepic text,
  ip text,
  dob timestamp without time zone,
  created timestamp without time zone DEFAULT now(),
  rawpassword character varying(128),
  rating integer,
  zip text,
  hash text,
  gender character(1),
  groups text,
  aim text,
  yahoo text,
  skype text,
  hotmail text,
  vanity text,
  number_comments integer DEFAULT 0,
  number_friends integer DEFAULT 0,
  number_posts integer DEFAULT 0,
  number_polls integer DEFAULT 0,
  city text,
  site text,
  number_pictures bigint DEFAULT 0,
  email_subscribe boolean DEFAULT true,
  number_userpics integer DEFAULT 0,
  htmlcodes text,
  pageviews integer DEFAULT 1,
  number_uservideos integer DEFAULT 0,
  number_useraudios integer DEFAULT 0,
  number_usermessages integer DEFAULT 0,
  number_usermessages_sent integer DEFAULT 0,
  myrand double precision NOT NULL DEFAULT random(),
  number_activities integer DEFAULT 0,
  number_pages integer DEFAULT 0,
  uid integer NOT NULL,
  number_invites integer DEFAULT 0,
  number_notifications integer DEFAULT 0,
  number_emailnotifications integer DEFAULT 0,
  last_invitation timestamp without time zone,
  last_emailnotification timestamp without time zone,
  total_number_invites integer DEFAULT 0,
  total_number_emailnotifications integer DEFAULT 0,
  number_cards_sent integer DEFAULT 0,
  number_cards_received integer,
  session_key text,
  number_cards_tosign integer DEFAULT 0,
  number_pendingnotifications integer DEFAULT 0,
  last_profilefbml timestamp without time zone,
  "name" text,
  email_bday_alert boolean DEFAULT true,
  last_email_bday_alert timestamp without time zone,
  last_bday_notified text,
  bday_alert_14 boolean DEFAULT false,
  bday_alert_3 boolean DEFAULT true,
  bday_alert_7 boolean DEFAULT false,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT unique_uid UNIQUE (uid),
  CONSTRAINT uniquemail UNIQUE (email),
  CONSTRAINT uniquuser UNIQUE (username)
)
WITH (OIDS=FALSE);
ALTER TABLE users OWNER TO postgres;

-- Index: idx_session_key

-- DROP INDEX idx_session_key;

CREATE INDEX idx_session_key
  ON users
  USING btree
  (session_key);

-- Index: idx_uid

-- DROP INDEX idx_uid;

CREATE INDEX idx_uid
  ON users
  USING btree
  (uid);

-- Index: idx_username

-- DROP INDEX idx_username;

CREATE INDEX idx_username
  ON users
  USING btree
  (username);

-- Index: last_login

-- DROP INDEX last_login;

CREATE INDEX last_login
  ON users
  USING btree
  (last_login);

-- Index: myrand_users

-- DROP INDEX myrand_users;

CREATE INDEX myrand_users
  ON users
  USING btree
  (myrand, id);

-- Index: pageviews_idx

-- DROP INDEX pageviews_idx;

CREATE INDEX pageviews_idx
  ON users
  USING btree
  (pageviews);

-- Index: user_sex_idx

-- DROP INDEX user_sex_idx;

CREATE INDEX user_sex_idx
  ON users
  USING btree
  (gender);


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general