Re: select query takes 13 seconds to run with index

mark wrote:

On Mon, May 26, 2008 at 4:26 PM, Justin <justin@emproshunts.com> 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 

I have no experience  on large datasets so people with more experience in this area are going to have to chime in.
My gut feel is 13 seconds for Postgresql to sort through an index of that size and table is not bad. 

you may need to take a look at hardware and postgresql.config settings to improve the performance for this query

This query is very simple where changing it around or adding index results massive improvements is not going to help in this case.

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? 

How many users are attached to the server at any given time.  how many inserts, deletes selects are being done on the server.  Its number  TPS  on the server.