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? 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
