Michael, The effective_cache_size, random_page_cost, work_mem were set to default. (commented). I have changed the setting of these and now the performance is better see below.
"HashAggregate (cost=42573.89..42925.52 rows=20093 width=37) (actual time=5273.984..5430.733 rows=55717 loops=1)" " -> Seq Scan on keyword_conversion_table c (cost=0.00..27336.12 rows=870730 width=37) (actual time=0.052..1405.576 rows=885493 loops=1)" " Filter: ((conversion_date >= '2005-06-07'::date) AND (conversion_date <= '2005-08-17'::date))" "Total runtime: 5463.764 ms" Thanks a lot --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Mon, Aug 29, 2005 at 11:07:17AM -0700, asif ali > wrote: > > The database is on the same system. > > What I am doing is only "VACUUM analyze > > conversion_table" > > > > I did the the same thing on a newly created > database. > > And got the same result. So after "VACUUM analyze" > > performance dropped. > > Please see this. Runtime changes from "7755.115" > to > > "14859.291" ms > > As has been pointed out a couple of times, you're > getting a different > plan after VACUUM ANALYZE because the row count > estimates are more > accurate. Unfortunately the more accurate estimates > result in a > query plan that's slower than the plan for the less > accurate > estimates. PostgreSQL *thinks* the plan will be > faster but your > results show that it isn't, so you might need to > adjust some of the > planner's cost constants. > > A asked some questions that you didn't answer, so > I'll ask them again: > > What's your effective_cache_size setting? > What's your work_mem (8.x) or sort_mem (7.x) > setting? > What's your random_page_cost setting? > How much available RAM does the machine have? > What version of PostgreSQL are you running? > > Various tuning guides give advice on how to set the > above and other > configuration variables. Here's one such guide: > > http://www.powerpostgresql.com/PerfList/ > > -- > Michael Fuhr > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
