To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,  Robert Haas <robertmhaas@gmail.com>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	"jd\@commandprompt.com" <jd@commandprompt.com>,
	Josh Berkus <josh@agliodbs.com>,  Greg Smith <gsmith@gregsmith.com>,
	pgsql-hackers@postgresql.org
Subject: Re: benchmarking the query planner
In-Reply-To: <1229033073.13078.179.camel@hp_dx2400_1> (Simon Riggs's message
	of "Thu\, 11 Dec 2008 22\:04\:33 +0000")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <603c8f070812061019r5aca3aajeaa5493b11c5feff@mail.gmail.com>
	<9897C4AF-6D47-4F44-AD3D-F5A82AC392DB@enterprisedb.com>
	<87ljuq4rmk.fsf@oxford.xeocode.com>
	<603c8f070812092025k6325e0fcud001b205a507a634@mail.gmail.com>
	<4313.1228931932@sss.pgh.pa.us>
	<603c8f070812101828p1bd7df49yb8c916adedb49fe1@mail.gmail.com>
	<9565.1229018968@sss.pgh.pa.us>
	<1229033073.13078.179.camel@hp_dx2400_1>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Thu, 11 Dec 2008 22:29:38 +0000
Message-ID: <87ej0etkfh.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Simon Riggs <simon@2ndQuadrant.com> writes:

> On Thu, 2008-12-11 at 13:09 -0500, Tom Lane wrote:
>
>> On the whole I think we have some evidence here to say that upping the
>> default value of default_stats_target to 100 wouldn't be out of line,
>> but 1000 definitely is.  Comments?
>
> Sounds good to me.
>
> I would like it even more if there was a data type specific default.
> Currently we have a special case for boolean, but that's it. TPC allows
> for different defaults for different types (but that's not my only
> reason). That would allow us to set it lower for long text strings and
> floats and potentially higher for things like smallint, which is less
> likely as a join target. 

In conjunction with a toast rethink it would be interesting to engineer things
so that we keep one page's worth of data after toasting for each of the
arrays. That would at least remove a lot of the dangers of larger arrays.

> Would be great if we could set the default_stats_target for all columns
> in a table with a single ALTER TABLE statement, rather than setting it
> for every column individually.

Hm, just because one column has a skewed data distribution doesn't tell you
much about what the other columns' data distributions are. On the other hand I
could see an argument that a given table might be consistently used in only
large batch queries or quick OLTP queries.

> And I would like it even more if the sample size increased according to
> table size, since that makes ndistinct values fairly random for large
> tables.

Unfortunately _any_ ndistinct estimate based on a sample of the table is going
to be pretty random.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!