[Info-Ingres] Slow running queries....

[Karl & Betty Schendel]

At 7:10 AM -0700 5/24/07, Manny wrote:

>Optimize stats are only available on the key columns - Should they
>also be on the segment columns?  I don't know anything about
>statistics - how are they used and is there an overhead?

Yes, stats on the segmentN columns might help.  Generally, statistics
help the optimizer make row estimates, which in turn produce
cost estimates.  Without any statistics the optimizer uses
defaults (i.e. wild guesses).  For example, it will assume
that the "b.segmentN = <constant>" clauses will restrict
table_2 to about 20000 rows -- 1% of 2 million.  Equality
predicates assume 1% of rows match (unless there is an obvious
unique-structure constraint), range predicates assume 10%,
!= assumes 50%.  Join predicates have a somewhat more
complicated default assumption that I don't remember at
the moment.

>
>I would have liked to see how you would have tackled the problem,
>nevertheless you have given me some pointers and I will try them out
>solely with the intention of improving my coding skills.

If you are not on a hard deadline, you might find it instructive
to play around with the query as it stands first:  get the QEP,
see if repeated helps, see if stats change the query plan.

Then, try revising the approach from a row loop driven
scheme to a temp-table based, set-wise scheme and see
what improvement you get.  Typically, you'll find that
tuning and fiddling will give you percentage improvements
(10%, 50%, whatever), while going to a set-wise approach
will get you factors of 2, 3, 10, and up.  I can cite any
number of extreme examples, like taking a 27 hour process
down to 10 minutes by rewriting row loops into set logic.
There are always exceptions, of course.

If you aren't used to reading Ingres QEP's, I believe
Roy has mentioned a few of the presentation resources
on the rationalcommerce.com website.

Karl