In response to Phoenix Kiula <phoenix.kiula@xxxxxxxxx>: > On Tue, Jul 21, 2009 at 6:12 PM, Peter Eisentraut<peter_e@xxxxxxx> 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@xxxxxxx> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
