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

On Mon, Jul 20, 2009 at 2:04 PM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> 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.

How did you tell that the row estimate is off? I mean which numbers?

Also, my statistics are already set pretty high. On "USER_ID" they are
at 100. Which columns should I increase the stats on, those in the
WHERE clause or those in the SELECT bit? The USER_ID and the
USER_KNOWN, which are in the WHERE clause, both have statistics of
100! And USER_KNOWN is just a binary value (0 or 1) so I wonder what
purpose stats would serve?