Re: AW: AW: AW: [HACKERS] Some notes on optimizer cost estimates

At 10:14 26/01/00 -0800, Don Baccus wrote:
>At 01:25 PM 1/26/00 +0100, Zeugswetter Andreas SB wrote:
>
>>Yes, I think syntax to force or disallow a particular index,
>>choose a join method or order, force/disallow seq scans ...
>>is sometimes useful.
>>Even Informix, who always refused to supply such a feature
>>now has it.
>
>Can you give some sample syntax, for those of us who aren't
>really database people but merely trying to maintain a facade? :)

There are two schemes I know if, one is the Microsoft (and I think Orcale)
way, and is, I believe the wrong way to go. It involves putting the
optimizer hints in the SQL statement. I have not used it, but they have
additional keywords in the table list, at the end of the query, and in the
join type. You can specify the order of tables, the type of cacheing etc
etc. I can cut & paste the relevant manual pages if people are *really*
interested.

This has the disadvantage that the SQL is far less portable (something I
imaging the vendor likes), and the further problem that if you want to
change the query hints, you have to change the SQL. Many apps have embedded
SQL (especially MS apps), and this is a real pain.

The other (better) scheme involves defining saved query plans. The database
can dump a subset of it's optimizer plan into a (hard to read) text format,
which can then be edited and used in a 'CREATE PLAN' statement. Part of a
plan definition is (I guess) an MD5 hash of the canonical form of the
query, and whenever a qeury is called for the first time, the hash is
calculated to check if a plan already exists. This is further extended by
the ability to define alternate plans, optional & mandatory plans etc etc.

Not that I am suggesting the whole box and dice in the first pass, but,
assuming the optimizer code would support the application of external plans
& hints, maybe we could define the table & index join order and join type.

For those people who know the internals of PGSQL, I'd be interested in
knowing if this is (1) feasible given the optimizer design, (2) in keeping
with the philosopy & design, and (3) how much it might cost (roughly) if
one were inclined to make a donation, and someone were inclined to do it...

>- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
> Nature photos, on-line guides, Pacific Northwest
> Rare Bird Alert Service and other goodies at
> http://donb.photo.net.
>
>************
>
>
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/