I put up the appropriate files at:

http://www.benziegler.com/stuff

files are sql_slow.txt, sql_fast.txt, and database_schema.txt

I can see which plan is being used in each case (I use Database
Workbench), and as expected there's a "natural" in the slow one, while
the fast uses an index. I tried forcing use of fast plan in the slow
query, but it said it the index wasn't allowed.

Fast Plan:
PLAN SORT (JOIN (JOIN (JOIN (A INDEX (I_GAME_PLAYERS_PID),B INDEX
(PK_PLAYERS)),G INDEX (GAME_PK)),C INDEX (PK_GAME_TYPE)))

Slow Plan:
PLAN SORT (JOIN (JOIN (JOIN (V_POSITION_SMRY A NATURAL,V_POSITION_SMRY
B INDEX (PK_PLAYERS)),V_POSITION_SMRY G INDEX
(GAME_PK)),V_POSITION_SMRY C INDEX (PK_GAME_TYPE)))


Its really frustrating, because in "theoritcal terms", the fast query
and slow query are identical and should use the same plan. I just
don't see why Firebird is getting confused on it.

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:
>
> > But if I don't use the view, and just take the SQL that makes up
> > V_VIEW and put the "where ID = X" into that SQL, it is FAST
> >
> > any idea why that would happen?
> >
> > V_VIEW looks like sort of like this:
> >
> > select ID, A, B, t2.C, sum(t2.D)
> > from table1 t1
> > left join table2 t2 on t1.ID = t2.ID
> > group by ID, A, B, C
>
> Which ID field do you think it is selecting; T1.ID or T2.ID? Now
> which one is it actually selecting?
>
> They may equal the same value, but if it is picking T2.ID then the
> optimiser must do a full table scan on T1 which would explain the
> slowness
>
> Also because it is obviously a join across PKs, left join and join
> will be identical, so using a normal inner join does not tie the
> optimisers hands to choosing a bad plan.
>
> Clearly because it runs faster outside the view a better plan does
> exist for this query.
>
> Try this:
>
> Replace the ? with the applicable table number
>
> select T1.ID, T?.A, T?.B, t2.C, sum(t2.D)
> from table1 t1
> join table2 t2 on t1.ID = t2.ID
> group by T1.ID, T?.A, T?.B, T2.C
>
> And see if it helps. Otherwise, post the entire Create View statement
> so we can know what field matches what.
>
> I also recommend you use a tool that can analyse a query plan and
> show you which table records are hit. IBPlanalyzer is a nice
> lightweight one, IBAdmin has one too.
>
> Hope that helps
> Adam