Re: Slow first query despite LIMIT and OFFSET clause

On Mon, Jan 26, 2009 at 2:26 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Sun, Jan 25, 2009 at 8:41 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>
>> My query is:
>>
>>
>> explain analyze SELECT
>> testimonials.url
>> ,testimonials.alias
>> ,testimonials.aliasEntered
>> ,testimonials.title
>> ,testimonials.modify_date
>> ,testimonials.id
>> ,visitcount.visit_count
>> ,visitcount.unique_count
>> ,visitcount.modify_date
>> ,coalesce( extract(epoch from now()) - extract(epoch
>> from visitcount.modify_date), 0)
>> ,(select count(id) from testimonials WHERE
>> testimonials.user_id = 'superman' and testimonials.user_known = 1 and
>> testimonials.status = 'Y' ) AS total
>> FROM testimonials
>> LEFT JOIN visitcount ON testimonials.id = visitcount.id
>> WHERE
>> testimonials.user_id = 'superman'
>> and testimonials.user_known = 1
>> and testimonials.status = 'Y'
>> ORDER BY testimonials.modify_date desc
>> OFFSET 0 LIMIT 10
>>
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------------------------------------------
>> Limit (cost=224.68..224.71 rows=10 width=187) (actual
>> time=453.429..453.539 rows=10 loops=1)
>> InitPlan
>> -> Aggregate (cost=63.52..63.53 rows=1 width=8) (actual
>> time=89.268..89.271 rows=1 loops=1)
>> -> Index Scan using new_idx_userknown on testimonials
>> (cost=0.00..63.41 rows=42 width=8) (actual time=0.039..49.968
>> rows=10149 loops=1)
>> Index Cond: ((user_id)::text = 'superman'::text)
>> Filter: (status = 'Y'::bpchar)
>> -> Sort (cost=161.16..161.26 rows=42 width=187) (actual
>> time=453.420..453.464 rows=10 loops=1)
>> Sort Key: testimonials.modify_date
>> -> Nested Loop Left Join (cost=0.00..160.02 rows=42
>> width=187) (actual time=89.384..395.008 rows=10149 loops=1)
>> -> Index Scan using new_idx_userknown on testimonials
>> (cost=0.00..63.41 rows=42 width=171) (actual time=0.061..50.990
>> rows=10149 loops=1)
>> Index Cond: ((user_id)::text = 'superman'::text)
>> Filter: (status = 'Y'::bpchar)
>> -> Index Scan using visitcount_pkey1 on visitcount
>> (cost=0.00..2.28 rows=1 width=24) (actual time=0.007..0.010 rows=1
>> loops=10149)
>
> Have you analyzed these tables? The estimates and real row counts are
> quite different.
>

Hi Scott. Yes, there is an autovacuum on both the tables. Should i
additionally do a manual vacuum too?