Re: First query very slow. Solutions: memory, or settings, or SQL? - Mailing list pgsql-general

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.