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? :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 26 Jan 2000 10:14:27 -0800
Don Baccus <dhogaza@pacifier.com>
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@pacifier.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   |/

Thu, 27 Jan 2000 09:56:27 +1100
Philip Warner <pjw@rhyme.com.au>
Re: AW: AW: AW: [HACKERS] Some notes on optimizer cost
  estimates
Philip Warner wrote:
> 
> 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)

IIRC Oracle hides the optimiser hints in comments, at least it does not 
directly sabotage portability.

I just remember reading an article about this in a magazine, have never used 
it myself.

-----------
Hannu

Thu, 27 Jan 2000 11:39:57 +0200
Hannu Krosing <hannu@tm.ee>
Re: AW: AW: AW: [HACKERS] Some notes on optimizer cost estimates