On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote: > However, now we have a query that is much slower with 8.1 compared to 7.4. > The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage > at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 > seconds the following tries. > We're not experts at all but we can't see anything strange with the > differences of EXPLAIN in the queries. Below I paste the query and the > EXPLAIN output. Could you post the EXPLAIN ANALYZE output of the query on both systems? That'll show how accurate the planner's estimates are. Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both versions? The row count estimates in the 8.1.1 query differ from those in the 7.4.7 query. Are the two versions using the same data set? Are your configuration settings the same in both versions? You mentioned increasing work_mem, but what about others like effective_cache_size, random_page_cost, and shared_buffers? -- Michael Fuhr
