Re: select query takes 13 seconds to run with index

mark wrote:
> On Mon, May 26, 2008 at 4:26 PM, Justin <justin(at)emproshunts(dot)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.