[Louis] Also, if I remove the order by, IP reports a drastic reduction is
execution time i.e. down to 6msec. Does the ORDER BY on the PK make
such a
difference?

[Set]My guess would rather be that the ORDER BY forces Fb to fetch all rows,
whereas without ORDER BY only the first few (random) rows are fetched,
and that the time for fetching all rows rather than the first row are
similar.

[Louis] Unlikely, the table has millions of rows…



[Ann] You seem to use left outer joins and that can be a problem because (in
general) the order in which outer joins are declared in the query is the
order in which they have to be accessed in the query plan.

[Set] Sure, LEFT JOIN limits the optimizer. However, the WHERE clause only
refers to the TILD (main) table, and the three LEFT JOINs seem to have a
function similar to subselects used to find lookup values (the last
three fields in the view). Hence, I'm pretty certain the optimizer would
have chosen a very similar plan if LEFT had been deleted (it could of
course have reordered the three "lookup tuples", but they're not related
anyway (they each join to the TILD table, not eachother), so the same
indexes should be used.



[Louis] Correct, the view is just a select from the main table with some joins to ‘flatten’ it out so that immediate lookups are pulled in.


Set





[Non-text portions of this message have been removed]