Re: A query planner that learns

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/13/06 10:47, John D. Burger wrote:
> Erik Jones wrote:
>
[snip]
> But with both approaches, the planner is just using the static
> statistics gathered by ANALYZE to estimate the cost of each candidate
> plan, and these statistics are based on sampling your data - they may be
> wrong, or at least misleading. (In particular, the statistic for total
> number of unique values is frequently =way= off, per a recent thread
> here. I have been reading about this, idly thinking about how to
> improve the estimate.)

What about an ANALYZE FULL <table>, reading every record in the
table, and ever node in every index, storing in pg_statistic (or
some new, similar table) such items as the AVG and STD of the number
of records in each page, and b-tree depth, keys per node, records
per key and per segment? Maybe even "average distance between pages
in the tablespace".

This would let the optimizer know things like "the value of the
field which is first segment of an index (and which is the only part
of the index in the WHERE clause) describes 75% of the rows in the
table, and the records are all packed in tightly in the pages, and
the pages are close together", so the optimizer could decide "a
table scan would be much more efficient".

In some ways, this would be similar in functionality to the existing
histogram created by ANALYZE, but would provide a slightly different
picture.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFMcu5S9HxQb37XmcRAvVJAJ0VFfEoxwrKn15VqPaZz54SNY4tPACg47zB
r3hZ+HqHE/1bCJK/xNZzNRE=
=OP9+
-----END PGP SIGNATURE-----