To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>,  Tom Lane <tgl@sss.pgh.pa.us>,
	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: <1229100057.8673.29.camel@ebony.2ndQuadrant> (Simon Riggs's
	message of "Fri\, 12 Dec 2008 16\:40\:57 +0000")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <603c8f070812061019r5aca3aajeaa5493b11c5feff@mail.gmail.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>
	<87ej0etkfh.fsf@oxford.xeocode.com>
	<1229039044.13078.193.camel@hp_dx2400_1>
	<4136ffa0812111823u645b6ec9wdca60b3da4b00499@mail.gmail.com>
	<1229072675.13078.196.camel@hp_dx2400_1>
	<603c8f070812120344m67ef2c1fs41806cfb4ff9e396@mail.gmail.com>
	<1229093975.13078.275.camel@ebony.2ndQuadrant>
	<87tz99s7bu.fsf@oxford.xeocode.com>
	<1229100057.8673.29.camel@ebony.2ndQuadrant>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Fri, 12 Dec 2008 17:05:58 +0000
Message-ID: <87hc59s4qx.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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

> The amount of I/O could stay the same, just sample all rows on block.
> Lifting the sample size will help large tables. Will it be perfect? No.
> But I'll take "better" over "not working at all".

That will just raise the table size at which the problems start. It'll still
be a constant-sized sample.

It will also introduce strange biases. For instance in a clustered table it'll
think there are a lot more duplicates than there really are because it'll see
lots of similar values.

Incidentally we *do* do block sampling. We pick random blocks and then pick
random records within those blocks. This was new in, uh, 7.4? 8.0? Sometime
around then. It dramatically reduced the i/o requirements but there were long
discussions of how to do it without introducing biases.

> If we are going to quote literature we should believe all the
> literature. We can't just listen to some people that did a few tests
> with sample size, but then ignore the guy that designed the MS optimizer
> and many others.

I'm not sure what you're talking about regarding "some people that did a few
tests". I looked around for the paper I keep referencing and can't find it on
my laptop. I'll look for it online. But it included a section which was a
survey of past results from other papers and the best results required
stupidly large sample sizes to get anything worthwhile. 

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning