pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndQuadrant.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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 16:40:57 +0000
Message-ID: <1229100057.8673.29.camel@ebony.2ndQuadrant> (raw)
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>


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




view thread (89+ messages)  latest in thread

Message-ID: <1229100057.8673.29.camel@ebony.2ndQuadrant>
Permalink:  ../1229100057.8673.29.camel@ebony.2ndQuadrant/
Also on:    postgresql.org/message-id/1229100057.8673.29.camel@ebony.2ndQuadrant

 · 

reply
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, stark@enterprisedb.com, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, jd@commandprompt.com, josh@agliodbs.com, gsmith@gregsmith.com
  Subject: Re: benchmarking the query planner
  In-Reply-To: <1229100057.8673.29.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