Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 744A69FA19D for ; Sun, 31 Dec 2006 08:16:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 00400-06 for ; Sun, 31 Dec 2006 08:16:40 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id CB1BF9F93A2 for ; Sun, 31 Dec 2006 08:16:44 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so6644482nfb for ; Sun, 31 Dec 2006 04:16:43 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=hmiojHEnNTRj2pkVsGkEtRP0HE9QrLnCarPnOcxiABSGjIqF6JhePEagD9gZL4SmjugqnnjNJ+MBwWbMtA+bPCEDzkI56F55JDqJPopY3z73rMLcPjGzaHndcpH4BGmhwcqbPfammP383V13ttPA43yYTfjRkMwtJIrb9MsktO0= Received: by 10.78.193.19 with SMTP id q19mr1613548huf.1167567403112; Sun, 31 Dec 2006 04:16:43 -0800 (PST) Received: by 10.78.100.9 with HTTP; Sun, 31 Dec 2006 04:16:43 -0800 (PST) Message-ID: Date: Sun, 31 Dec 2006 13:16:43 +0100 From: "Claus Guttesen" To: "=?ISO-8859-1?Q?Rolf_=D8stvik?=" Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14 Cc: pgsql-performance@postgresql.org In-Reply-To: <200612311233.10201.rolfostvik@yahoo.no> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200612311233.10201.rolfostvik@yahoo.no> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/541 X-Sequence-Number: 22597 > I have a simple query which uses 32ms on 7.4.14 and 1015ms on 8.2.0. > I guess 7.4.14 creates a better execution plan than 8.2.0 for this query but > i don't know how to get it to select a better one. > Explain analyse output will be found near the end of the e-mail. > > Explain analyze is run several times to get a stable result > so i guess the numbers presented is with as much as possible > data in memory buffers. > > Query: "select * from view_subset;" run against 7.4.14 server. > QUERY PLAN > ------------------------------------------------------------------------ > Nested Loop (cost=0.00..1400.86 rows=17 width=8) (actual time=0.161..26.287 rows=68 loops=1) > -> Index Scan using uut_result_subset_start_date_time_idx on uut_result_subset ur (cost=0.00..63.28 rows=18 width=4) (actual time=0.052..0.195 rows=68 loops=1) > Index Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) > -> Index Scan using step_result_uut_result_idx on step_result_subset sr (cost=0.00..74.28 rows=2 width=8) (actual time=0.149..0.379 rows=1 loops=68) > Index Cond: ("outer".id = sr.uut_result) > Filter: (step_parent = 0) > Total runtime: 26.379 ms > > Query: "select * from view_subset;" run against 8.4.0 server. > > QUERY PLAN > ---------------------------------------------------------------------- > Hash Join (cost=339.61..77103.61 rows=96 width=8) (actual time=5.249..1010.669 rows=68 loops=1) > Hash Cond: (sr.uut_result = ur.id) > -> Index Scan using step_result_subset_parent_key on step_result_subset sr (cost=0.00..76047.23 rows=143163 width=8) (actual time=0.082..905.326 rows=176449 loops=1) > Index Cond: (step_parent = 0) > -> Hash (cost=339.31..339.31 rows=118 width=4) (actual time=0.149..0.149 rows=68 loops=1) > -> Bitmap Heap Scan on uut_result_subset ur (cost=4.90..339.31 rows=118 width=4) (actual time=0.060..0.099 rows=68 loops=1) > Recheck Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) > -> Bitmap Index Scan on uut_result_subset_start_date_time_idx (cost=0.00..4.90 rows=118 width=0) (actual time=0.050..0.050 rows=68 loops=1) > Index Cond: (start_date_time > '2006-12-11 00:00:00'::timestamp without time zone) > Total runtime: 1010.775 ms Did you lower random_page_cost in 8.2 (which defaults to 4.0)? If not try 2. regards Claus