Hi Jakub!

> Excuse me, but...
> 1) How did you come up with the idea that PLAN NATURAL = bad plan?

Have

> you ever tried to access 80% records of a 1gig table with indexed
> access?

IMHO, if the plan clause contains 1 word natural is not bad at all,
but if it contains more - it usually means badly designed join(s),
which result to cross product of tables. Although it could be index
statistics of course. I have looked at these joins who knows how
many times aleready :(

> 2) What are you trying to achieve with "Try again?" There's no "if
> (rand()>0.9) then slow_things_down_and_go_natural();." This is

Firebird,

> not Windows :)

Currently there is no possibility to stop running query, so a bad
query must not allowed to run at all. By "bad" I mean query, that it
takes 5 minutes or more, when it should take 1 sec. For now, the
query text(with the execution time) goes into log file and this way
I will be able to test things later. Message "BAD query plan, try
again" could be replaced by "hire another programmer", if you
like :) But it must not run and hang the server for 5 minutes.

The last problem I had, was a query, consisting of view (from ~15
joined tables) joined to 2 other tables. Users can't change the
joins, but they are free to set any filters to any columns - which
are converted to WHERE clause by program. Just a small example:
... WHERE (YLD.LO_ID IS NOT NULL) AND
((YLD.KUU IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)) OR
(YLD.KUU IS NULL)) AND
(YLD.AASTA > 0) AND
((YLD.NADALAPAEV IN (0, 1, 2, 3, 4, 5, 6, 7)) OR (YLD.NADALAPAEV
IS NULL)) AND
(OS.ONNETUS_ID IS NOT NULL) AND
((OS.OSALEJA_LIIK IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
14, 15, 16, 18, 19)) OR
(OS.OSALEJA_LIIK IS NULL)) AND
(NOT OS.SUGU IS NULL) AND
((OS.HARIDUS IN (0, 1, 2, 3, 4, 5, 6)) OR (OS.HARIDUS IS NULL)) AND
(KAN.ONNETUS_ID IS NOT NULL) AND
((KAN.KANNATANU_LIIK IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 18, 19))
OR (KAN.KANNATANU_LIIK IS NULL))
AND (NOT KAN.SUGU IS NULL)
This quey took 6 minutes(plan had 2xnatural). Without "where" clause
it ran ~1 sec(1xnatural). I removed all clauses and started to put
them back one by one. Would you beleive, that it was the "NOT"
inside the last clause. Without NOT it used index(1 sec), with NOT
it didn't(6 min). As changing of the order had no impact(as always)
to quey plan, I was out of ideas.

> You should either recomupute index statistics (which _could_

improve the

> situation if the data has drastically changed...)...

Yes, I know that. But I don't now _when_, what is the exact
criteria? All indexes, that are are not primary/foreign keys, are
switched off and back on after each data import, backup/restore is
done once a week ..

> ...or take a look at your data model, think about the query a bit,

look

> at some numbers (idx selectivities, row counts, expected

percentage of

> rows selected) and try to optimize it with hand (Hint! Hint!

Planalyzer

> http://delphi.weblogs.com/IBPLANalyzer :)...

yes, I know that also :-) it has this magic "set statistics" button
next to selectivity number, that one should try when s>1,5

> ...or - in some special cases - itmay be more efficient to employ

some

> SPs and cursors (and maybe even The One Mysterious Hidden

Column...but

> that's possibly an offshot, try optimizing the query first.)

I'm considering this new FireBird1.5 "execute varchar", but haven't
decided yet.

Thanks for your support.

regards,
Harri