Subject: Re: benchmarking the query planner
From: Simon Riggs <simon@2ndQuadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, Greg Stark <stark@enterprisedb.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
In-Reply-To: <5301.1229092509@sss.pgh.pa.us>
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>
	<5301.1229092509@sss.pgh.pa.us>
Content-Type: text/plain
Date: Fri, 12 Dec 2008 15:06:49 +0000
Message-Id: <1229094409.13078.283.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Fri, 2008-12-12 at 09:35 -0500, Tom Lane wrote:

> AFAICS, marginal enlargements in the sample size aren't going to help
> much for ndistinct --- you really need to look at most or all of the
> table to be guaranteed anything about that.
> 
> But having said that, I have wondered whether we should consider
> allowing the sample to grow to fill maintenance_work_mem, rather than
> making it a predetermined number of rows.  One difficulty is that the
> random-sampling code assumes it has a predetermined rowcount target;
> I haven't looked at whether that'd be easy to change or whether we'd
> need a whole new sampling algorithm.

I think we need to do block sampling before we increase sample size. On
large tables we currently do one I/O per sampled row, so the I/O cost of
ANALYZE would just increase linearly.

We need the increased sample size for ndistinct, not for other stats. So
I would suggest we harvest a larger sample, use that for ndistinct
estimation, but then sample-the-sample to minimise processing time for
other stats that aren't as sensitive as ndistinct.

Currently we fail badly on columns that have been CLUSTERed and we can
improve that significantly by looking at adjacent groups of rows, i.e.
block sampling.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support