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

In response to Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>:

> On Tue, Jul 21, 2009 at 6:12 PM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> > On Tuesday 21 July 2009 04:36:41 Phoenix Kiula wrote:
> >> 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?
> >
> > Compare the rows=N figures for the estimate and the actual time.
> >
> >> 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?
> >
> > Well then that should be OK.
>
> So what can I do to speed up? I now have about 8GB of memory (upgraded
> yesterday). Would it help to increase the following:
>
> shared_buffers = 512MB
> effective_cache_size = 3GB
>
> Both of these are conservative I think? My data size is about 30 GB
> right now. Vacuum is all autovacuum as you see from settings I shared.

With that much RAM, you should see improvement bumping shared_buffers
up to 2G or so (assuming that you're using a POSIX os).

> Any other ideas?

Does a manual analyze cause the query to run faster for a while? If so,
your autovacuum settings may not be analyzing aggressively enough. I came
late to the thread, so I don't know what your autovac settings were.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/