Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 767C79F9F47 for ; Sun, 31 Dec 2006 09:00:57 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 69671-10 for ; Sun, 31 Dec 2006 09:00:47 -0400 (AST) X-Greylist: delayed 00:06:39.34682 by SQLgrey-1.7.4 Received: from web26302.mail.ukl.yahoo.com (web26302.mail.ukl.yahoo.com [217.146.176.13]) by postgresql.org (Postfix) with SMTP id 78A949F93A2 for ; Sun, 31 Dec 2006 09:00:51 -0400 (AST) Received: (qmail 34073 invoked by uid 60001); 31 Dec 2006 12:54:10 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.no; h=Message-ID:X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=qmPGlXWT5LCVr4YKRblgNl1qMovO9i5DQ/Wj01ch8CaReO6he4weCvR9o5sDyMWPPNEApEv2gBLSC3M4RcnZA3MbjNm0p2iVIhXIVOtDle5blKv4NeYq+BTzPCJIEEmzLsCPcpI1EWT4BZdN6Ia/6I5BANPvcTxXsvyokWlAH7w= ; Message-ID: <20061231125410.34071.qmail@web26302.mail.ukl.yahoo.com> X-YMail-OSG: ov6LHE4VM1mTkT7d58U_VeVQ8KX6YbB15atSVd9G85ZCoO3K258FftckHal9EVRQe0K7iPOA8NvsKRKUGbrNzlHHFpvxchJ5vJII_cSdRmkJlEjoNL4pBl4IrK4tuKfjG4X8EySjoYAHVLFPwjbKkV8hFwJBQ1NZoZXq7iJI03oGFkXbEb98a919DQ-- Received: from [81.175.5.65] by web26302.mail.ukl.yahoo.com via HTTP; Sun, 31 Dec 2006 13:54:09 CET Date: Sun, 31 Dec 2006 13:54:09 +0100 (CET) From: =?iso-8859-1?q?Rolf=20=D8stvik?= Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14 To: pgsql-performance@postgresql.org Cc: Claus Guttesen In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/542 X-Sequence-Number: 22598 --- Claus Guttesen skrev: > > 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. Thanks for the suggestion, but it was no change of result. > regards > Claus __________________________________________________ Bruker du Yahoo!? Lei av spam? Yahoo! Mail har den beste spambeskyttelsen http://no.mail.yahoo.com