Subject: Re: benchmarking the query planner
From: Simon Riggs <simon@2ndQuadrant.com>
To: Gregory Stark <stark@enterprisedb.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
In-Reply-To: <87tz99s7bu.fsf@oxford.xeocode.com>
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>
Content-Type: text/plain
Date: Fri, 12 Dec 2008 16:40:57 +0000
Message-Id: <1229100057.8673.29.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Fri, 2008-12-12 at 16:10 +0000, Gregory Stark wrote:
> Right, but increasing our sample size by a factor of 150 for a 100M
> row table doesn't seem like a reasonable solution to one metric being
> bogus.
> 
> For that matter, if we do consider sampling 5% of the table we may as
> well just go ahead and scan the whole table. It wouldn't take much
> longer and it would actually produce good estimates.

As I said, we would only increase sample for ndistinct, not for others.

At the moment we completely and significantly fail to assess ndistinct
correctly on clustered data for large tables. Using block level sampling
would prevent that. Right now we may as well use a random number
generator.

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".

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.

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