Skip site navigation (1) Skip section navigation (2)

Re: select query takes 13 seconds to run with index

From: Justin <justin(at)emproshunts(dot)com>
To: mark <markkicks(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: select query takes 13 seconds to run with index
Date: 2008-05-26 23:49:08
Message-ID: 483B4C74.7000703@emproshunts.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance

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.

In response to

Responses

pgsql-performance by date

Next:From: markDate: 2008-05-26 23:57:06
Subject: Re: select query takes 13 seconds to run with index
Previous:From: markDate: 2008-05-26 23:32:50
Subject: Re: select query takes 13 seconds to run with index

pgsql-general by date

Next:From: markDate: 2008-05-26 23:57:06
Subject: Re: select query takes 13 seconds to run with index
Previous:From: markDate: 2008-05-26 23:32:50
Subject: Re: select query takes 13 seconds to run with index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group