Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CCA359FA4BB for ; Sat, 6 Jan 2007 06:18:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 04033-06 for ; Sat, 6 Jan 2007 06:18:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id CBE3B9FA315 for ; Sat, 6 Jan 2007 06:18:07 -0400 (AST) thread-index: Accxe/lGUMHnaXRUS+u6l2GBJf72GQ== Received: from [192.168.0.22] ([84.12.182.179]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 6 Jan 2007 05:18:12 -0500 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Content-Class: urn:content-classes:message Importance: normal Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14 From: "Simon Riggs" To: =?iso-8859-1?Q?Rolf_=D8stvik?= Cc: "Tom Lane" , In-Reply-To: <20070105182833.94277.qmail@web26312.mail.ukl.yahoo.com> References: <20070105182833.94277.qmail@web26312.mail.ukl.yahoo.com> Content-Type: text/plain; charset="utf-8" Date: Sat, 06 Jan 2007 10:17:04 +0000 Message-ID: <1168078625.3655.97.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: quoted-printable X-OriginalArrivalTime: 06 Jan 2007 10:18:12.0640 (UTC) FILETIME=[F8B07200:01C7317B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/76 X-Sequence-Number: 22677 On Fri, 2007-01-05 at 19:28 +0100, Rolf =C3=98stvik wrote: > --- Tom Lane skrev: >=20 > > The number-of-matching-rows estimate has gone up by a factor of 10, > > which undoubtedly has a lot to do with the much higher cost estimate. > > Do you have any idea why that is ... is the table really the same size > > in both servers? If so, could we see the pg_stats row for > > step_result_subset.uut_result on both servers? >=20 > Table step_result_subset and uut_result_subset in both databases is creat= ed from same schema > definition file and filled with data from the same data source file. >=20 > =3D=3D=3D=3D Server 7.4.14: =3D=3D=3D=3D >=20 > logistics_74# select count(*) from step_result_subset; > count =20 > ---------- > 17179506 > (1 row) >=20 > logistics_74# select count(distinct uut_result) from step_result_subset; > count =20 > -------- > 176450 > (1 row) >=20 > logistics_74# analyse verbose step_result_subset; > INFO: analyzing "public.step_result_subset" > INFO: "step_result_subset": 92863 pages, 3000 rows sampled, 17179655 est= imated total rows > ANALYZE >=20 > logistics_74# select * from pg_stats where tablename =3D step_result_subs= et and > attname=3D'uut_result'; > schemaname | tablename | attname | null_frac | avg_width | n= _distinct | =20 > most_common_vals | = =20 > most_common_freqs | = =20 > histogram_bounds | correlation=20 > ------------+--------------------+------------+-----------+-----------+--= ----------+----------------------------------------------------------------= +--------------------------------------------------------------------------= -------------------------------------+-------------------------------------= ------------------------------------+------------- > public | step_result_subset | uut_result | 0 | 4 | = 57503 | > {70335,145211,17229,20091,21827,33338,34370,42426,47274,54146} | > {0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,= 0.000666667,0.000666667,0.000666667} > | {213,30974,51300,68529,85053,100838,114971,128126,144230,161657,176691}= | 0.951364 > (1 row) >=20 >=20 > =3D=3D=3D=3D Server 8.2.0: =3D=3D=3D=3D >=20 > logistics_82# select count(*) from step_result_subset; > count =20 > ---------- > 17179506 > (1 row) >=20 > logistics_82# select count(distinct uut_result) from step_result_subset; > count =20 > -------- > 176450 > (1 row) >=20 > logistics_82# analyse verbose step_result_subset; > INFO: analyzing "public.step_result_subset" > INFO: "step_result_subset": scanned 3000 of 92863 pages, containing 5550= 00 live rows and 0 dead > rows; 3000 rows in sample, 17179655 estimated total rows > ANALYZE >=20 > logistics_# select * from pg_stats where tablename =3D step_result_subset= and attname=3D'uut_result'; > schemaname | tablename | attname | null_frac | avg_width | n= _distinct | =20 > most_common_vals | = =20 > most_common_freqs | = =20 > histogram_bounds | correlation=20 > ------------+--------------------+------------+-----------+-----------+--= ----------+----------------------------------------------------------------= -+-------------------------------------------------------------------------= ----------------------------------------+----------------------------------= ---------------------------------------+------------- > public | step_result_subset | uut_result | 0 | 4 | = 6516 | > {35010,111592,35790,41162,56844,57444,60709,73017,76295,106470} | > {0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.0013= 3333,0.00133333,0.00133333,0.00133333} > | {147,31791,54286,70928,85996,102668,117885,130947,144766,162098,176685}= | 0.954647 > (1 row) >=20 > Then on server 8.2.0 i need to set statistics to ~120 on step_result_subs= et.uut_result to get > n_distinct to be in same range as n_distinct on 7.4.14. >=20 > Even with a statistics value of 1000, the n_distinct value does only reac= h ~138 000. Is it correct > that _ideally_ the n_distinct value should be the same as "select count(d= istinct uut_result) from > step_result_subset"?=20 That is correct, as long as the number hasn't changed between the ANALYZE and the select. > Even with better statistics on step_result_subset.uut_result neither of 7= .4.14 or 8.2.0 manages to > pick the best plan when i want to select bigger datasets (in my examples = that would be to set an > earlier date in the where clause for "ur.start_date_time > '2006-12-11'")= . I will continue to > adjust other parameters and see what i can manage myself. The ndistinct figure is very sensitive. Could you re-run ANALYZE say 10 times each on the two release levels? That will give you a better feel for the spread of likely values. The distribution of rows with those values also makes a difference to the results. ANALYZE assumes that all values are randomly distributed within the table, so if the values are clumped together for whatever reason the ndistinct calc is less likely to take that into account. The larger sample size gained by increasing stats target does make a difference. --=20 Simon Riggs =20 EnterpriseDB http://www.enterprisedb.com