On Mon, May 26, 2008 at 4:26 PM, Justin <justin@xxxxxxxxxxxxxxx> wrote: > mark wrote: >> Hi, is there anyway this can be made faster? id is the primary key, >> and there is an index on uid.. >> thanks >> EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id >> DESC limit 6; >> QUERY >> PLAN >> >> ---------------------------------------------------------------------------------------------------------------------------------------------------- >> Limit (cost=0.00..9329.02 rows=6 width=135) (actual >> time=13612.247..13612.247 rows=0 loops=1) >> -> Index Scan Backward using pokes_pkey on pokes >> (cost=0.00..5182270.69 rows=3333 width=135) (actual >> time=13612.245..13612.245 rows=0 loops=1) >> Filter: (uid = 578439028) >> Total runtime: 13612.369 ms >> (4 rows) > First this should be posted on performance list. sorry about this. > how many records are in this table? 22334262, 22 million records. > The estimate is way off, when was the last time Vaccum was on the table? about a week ago i ran this VACUUM VERBOSE ANALYZE; this table is never updated or deleted, rows are just inserted... > What verison of Postgresql are you running 8.3.1 > Size of the Table 22 million rows approximately > Table layout CREATE TABLE pokes ( id serial NOT NULL, uid integer, action_id integer, created timestamp without time zone DEFAULT now(), friend_id integer, message text, pic text, "name" text, CONSTRAINT pokes_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); ALTER TABLE pokes OWNER TO postgres; -- Index: idx_action_idx -- DROP INDEX idx_action_idx; CREATE INDEX idx_action_idx ON pokes USING btree (action_id); -- Index: idx_friend_id -- DROP INDEX idx_friend_id; CREATE INDEX idx_friend_id ON pokes USING btree (friend_id); -- Index: idx_pokes_uid -- DROP INDEX idx_pokes_uid; CREATE INDEX idx_pokes_uid ON pokes USING btree (uid); > Load on the database how do i measure load on database?
