Re: First query very slow. Solutions: memory, or settings, or SQL?

On Monday 20 July 2009 06:45:40 Phoenix Kiula wrote:
> explain analyze select * from sites where user_id = 'phoenix' order by
> id desc limit 10;
>
> QUERY PLAN
> ---------------------------------------------------------------------------
>----------------------------------------------------------- Limit
> (cost=344.85..344.87 rows=10 width=262) (actual
> time=5879.069..5879.167 rows=10 loops=1)
> -> Sort (cost=344.85..345.66 rows=323 width=262) (actual
> time=5879.060..5879.093 rows=10 loops=1)
> Sort Key: id
> -> Index Scan using new_idx_sites_userid on sites
> (cost=0.00..331.39 rows=323 width=262) (actual time=44.408..5867.557
> rows=2178 loops=1)
> Index Cond: ((user_id)::text = 'phoenix'::text)
> Total runtime: 5879.414 ms
> (6 rows)

The row estimate for the index scan is off. Try reanalyzing, or increase the
statistics target.