From: =?iso-8859-1?q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com>
Organization: dalibo.com
To: pgsql-hackers@postgresql.org
Subject: Re: Maximum statistics target
Date: Mon, 10 Mar 2008 11:58:37 +0100
User-Agent: KMail/1.9.9
Cc: Peter Eisentraut <peter_e@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>,
	Martijn van Oosterhout <kleptog@svana.org>
References: <200803071925.26532.peter_e@gmx.net>
	<28138.1204926525@sss.pgh.pa.us>
	<200803101136.06204.peter_e@gmx.net>
In-Reply-To: <200803101136.06204.peter_e@gmx.net>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart2706619.WJFaAesZsC";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200803101158.42022.cedric.villemain@dalibo.com>

--nextPart2706619.WJFaAesZsC
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Le Monday 10 March 2008, Peter Eisentraut a =E9crit=A0:
> Am Freitag, 7. M=E4rz 2008 schrieb Tom Lane:
> > I'm not wedded to the number 1000 in particular --- obviously that's
> > just a round number.  But it would be good to see some performance tests
> > with larger settings before deciding that we don't need a limit.
>
> Well, I'm not saying we should raise the default statistics target.  But
> setting an arbitrary limit on the grounds that larger values might slow t=
he
> system is like limiting the size of tables because larger tables will cau=
se
> slower queries.  Users should have the option of finding out the best
> balance for themselves.  If there are concerns with larger statistics
> targets, we should document them.  I find nothing about this in the
> documentation at the moment.

I find 2 things:
=ABIncreasing the target causes a proportional increase in the time and spa=
ce=20
needed to do ANALYZE. =BB
in http://www.postgresql.org/docs/current/static/sql-analyze.html
and
=AB ... at the price of consuming more space in pg_statistic and slightly m=
ore=20
time to compute the estimates=BB
in http://www.postgresql.org/docs/current/static/planner-stats.html

But probably not clear enought about time impact in query plan.


>
> > IIRC, egjoinsel is one of the weak spots, so tests involving planning of
> > joins between two tables with large MCV lists would be a good place to
> > start.
>
> I have run tests with joining two and three tables with 10 million rows
> each, and the planning times seem to be virtually unaffected by the
> statistics target, for values between 10 and 800000.  They all look more =
or
> less like this:
>
> test=3D# explain select * from test1, test2 where test1.a =3D test2.b;
>                                  QUERY PLAN
> -------------------------------------------------------------------------=
=2D-
>-- Hash Join  (cost=3D308311.00..819748.00 rows=3D10000000 width=3D16)
>    Hash Cond: (test1.a =3D test2.b)
>    ->  Seq Scan on test1  (cost=3D0.00..144248.00 rows=3D10000000 width=
=3D8)
>    ->  Hash  (cost=3D144248.00..144248.00 rows=3D10000000 width=3D8)
>          ->  Seq Scan on test2  (cost=3D0.00..144248.00 rows=3D10000000
> width=3D8) (5 rows)
>
> Time: 132,350 ms
>
> and with indexes
>
> test=3D# explain select * from test1, test2 where test1.a =3D test2.b;
>                                          QUERY PLAN
> -------------------------------------------------------------------------=
=2D-
>----------------- Merge Join  (cost=3D210416.65..714072.26 rows=3D10000000
> width=3D16)
>    Merge Cond: (test1.a =3D test2.b)
>    ->  Index Scan using test1_index1 on test1  (cost=3D0.00..282036.13
> rows=3D10000000 width=3D8)
>    ->  Index Scan using test2_index1 on test2  (cost=3D0.00..282036.13
> rows=3D10000000 width=3D8)
> (4 rows)
>
> Time: 168,455 ms
>
> The time to analyze is also quite constant, just before you run out of
> memory. :)  The MaxAllocSize is the limiting factor in all this.  In my
> example, statistics targets larger than about 800000 created pg_statistic
> rows that would have been larger than 1GB, so they couldn't be stored.
>
> I suggest that we get rid of the limit of 1000, adequately document
> whatever issues might exist with large values (possibly not many, see
> above), and add an error message more user-friendly than "invalid memory
> alloc request size" for the cases where the value is too large to be
> storable.


=2D-=20
C=E9dric Villemain
Administrateur de Base de Donn=E9es
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

--nextPart2706619.WJFaAesZsC
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBH1RRho/dppWjpEvwRAr7TAJ9/5GWyjiZoSQCtbmluwHkKXipWyQCfffQC
rcjW499jUsJz3zuv2TlfSXU=
=uPek
-----END PGP SIGNATURE-----

--nextPart2706619.WJFaAesZsC--