From: Simon Riggs <simon@2ndQuadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Greg Stark <stark@enterprisedb.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: jd@commandprompt.com <jd@commandprompt.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: benchmarking the query planner
Date: Fri, 12 Dec 2008 15:06:49 +0000
Message-ID: <1229094409.13078.283.camel@ebony.2ndQuadrant> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: simon@2ndQuadrant.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, stark@enterprisedb.com, Kevin.Grittner@wicourts.gov, jd@commandprompt.com, josh@agliodbs.com, gsmith@gregsmith.com
Subject: Re: benchmarking the query planner
In-Reply-To: <1229094409.13078.283.camel@ebony.2ndQuadrant>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox