On Wed, 2005-08-10 at 13:31 -0700, Shane wrote: > On Wed, Aug 10, 2005 at 04:24:51PM -0400, Sven Willenberger wrote: > > On Wed, 2005-08-10 at 12:58 -0700, Shane wrote: > > > On Wed, Aug 10, 2005 at 03:31:27PM -0400, Sven Willenberger wrote: > > > > Right off the bat (if I am interpreting the results of your explain > > > > analyze correctly) it looks like the planner is basing its decision to > > > > seqscan as it thinks that it needs to filter over 1 million rows (versus > > > > the 29,000 rows that actually are pulled). Perhaps increasing stats on > > > > msgtime and then analyzing the table may help. Depending on your > > > > hardware, decreasing random_page_cost in your postgresql.conf just a > > > > touch may help too. > > > > Try increasing stats to 100 on just the msgtime column, not the default > > (changing the default will only have an effect on newly created columns > > -- you may want to change the default back to 10): > > Hi, > > I brought the statistics on msgtime up to 100, vacuum > analyzed and brought random_page_cost down to 2. > Unfortunately, explain analyze still wants to seqscan and > estimates 1m returned rows. > > Is there a way to simply force an index usage for this > particular query? > > S > What version of PostgreSQL are you running? Also, what happens if you explain analyze choosing where msgtime > cast(now() - interval '6 months' as timestamp(0) without time zone); (instead of less than). Depending on how you are connecting to run this query (script, webpage, psql) you could always set enable_seq_scan=off; select ....; set enable_seq_scan=on; scriptomagically. Sven ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
