Re: pg_advisor schema proof of concept

Dear Tom,

> This is necessarily so, as the information_schema by definition covers
> only concepts standardized by the SQL spec. Since the SQL spec
> considers things like indexes to be implementation details, it is simply
> not possible for information_schema to tell you everything you want to
> know to give performance advice.

Well, it makes sense.

As pg_catalog will be necessary for some advices, let us avoid
"information_schema" for a greater homogeneity.

> >> If plpgsql works OK, I say stick with it.
>
> > Hmmm. I'm not very happy with plpgsql,
>
> I don't know where you are planning on going with this. If it's only to
> be a contrib tool, it's okay to depend on plpgsql. But we couldn't
> incorporate it into the base system because plpgsql isn't part of the
> base system.

Well, the ultimate status of the tool basically depends on the patchers
("we" above) decision;-)

If you veto the inclusion of advisor stuff into the base system because
you do not want it there anyway, which may be perfectly legitimate, then I
would not bother to port the plpgsql stuff just for the fun of it.

On the otherhand, if you would be ready to consider it for inclusion in
the base system some day, provided that the quality is fine and that there
is no plpgsql in it, then it would make sense to discuss needed functions
to be added to the base system.

The current "preliminary" implementation requires plpgsql for :

- array_index (find index of item in array, to deal with pg_constraint
attribute lists)

- array_ceq (whether two arrays contains the same values, possibly in a
different order, idem)

- count_tuples (count the number of tuples in a relation)

I think these functions could be included in the base system, anyway.

As for "performance advices", such as missing indexes for ri check that
you suggested, I don't know.

Some functions that already exists in the backend would be welcome to be
called from sql, such as selecting an "=" operator variant given the oid
of the expected types... but maybe they can be developped within SQL (i.e.
without plpgsql). I haven't looked at it yet.

As for what is not foreseen yet, who knows? ;-)

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr