Re: Slow first query despite LIMIT and OFFSET clause

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.