Svein,

I'll try to enforce the optimizer not to use those indices and will
write about the results. Left join is necessary because the query that
you've seen is a simplified version of the actual query (the actual
query contains two unions and more complex "where" clauses, involves
other two "action types" and subscribers without records in
"last_actions" table).

Thank you.

Svein Erling Tysvaer wrote:

> Hi Andrew, mind if I add a few remarks even though I'm not a Firebird
> 2.0 guy?
>
> It isn't ideal to use several indexes for tables near the end of the
> plan, if one of them is on fields with low selectivity I'd try to avoid
> using the index altogether. In your case, that means i.type_id and
> l.action_id. Moreover, I don't see why you use LEFT [outer] JOIN and not
> a simple [inner] JOIN when referring to a field of the right table in
> the WHERE clause (it may occasionally be used for forcing a plan). In
> your case, they produce the same result. Finally, some of your logic is
> more suited in the where clause than the join clause (though I doubt
> this will influence the optimizer), and due to my own preference I moved
> a bit more that I'm uncertain whether belongs to the where or join
> clause ;o) Try the following and report back to the list the suggested
> plan and result:
>
> select c.id, c.name, /*c.email,*/ i.event_date, i.event_time,
> c.hot_flag, c.no_spam, /*a.name as aname,*/
> cast(1 as smallint) as atype, t.name as itype,
> /*a.subject as template,*/ a.id as action_id, t.id as type_id
> from customers c
> inner join interactions i on i.customer_id = c.id
> inner join cust_subscr s on s.customer_id = c.id
> and s.type_id = i.type_id+0
> inner join actions a on a.type_id = i.type_id+0
> inner join intr_types t on i.type_id = t.id
> inner join last_actions l on l.customer_id = c.id
> and l.action_id+0 = a.id
> /* and l.event_tsc > i.event_date*/
> where
> l.event_tsc <
> i.event_date+coalesce(i.event_time, cast('00:00' as time))+a.day_period
> and c.deleted = 0 and c.no_spam = 0
> and a.relative = 1 and a.deleted = 0 and a.is_active > 0
> and current_date-a.day_period >= i.event_date
>
> Now, I do not know anything about Firebird 2.0, the above changes are
> what I would have done to a Firebird 1.5 database. But it will be
> interesting to see what effect my suggested changes has on the result.
>
> HTH,
> Set
>
> Andrew Guts wrote:
>
>> Hello Firebird team,
>>
>> I have migrated existing application from Firebird 1.5.2 to 2.0 RC3 on
>> the same server. The database was restored from backup with new gbak.
>> There is a query that becomes too slow right after migration. Firebird
>> 1.5.2 executed the query in 2-4 seconds, but it takes about 25-40 sec
>> with Firebird 2.0. I have no idea why it happened: the database is
>> small, so number of records involved is not heavy.
>> Could you please clarify the situation?
>>
>> The query is:
>>
>> select c.id, c.name, /*c.email,*/ i.event_date, i.event_time,
>> c.hot_flag, c.no_spam, /*a.name as aname,*/
>> cast(1 as smallint) as atype, t.name as itype,
>> /*a.subject as template,*/ a.id as action_id, t.id as type_id
>> from customers c
>> inner join interactions i on i.customer_id = c.id
>> and c.deleted = 0 and c.no_spam = 0
>> inner join cust_subscr s on s.customer_id = c.id
>> and s.type_id = i.type_id
>> inner join actions a on a.type_id = i.type_id
>> and a.relative = 1 and a.deleted = 0 and a.is_active > 0
>> and current_date-a.day_period >= i.event_date
>> inner join intr_types t on i.type_id = t.id
>> left join last_actions l on l.customer_id = c.id and l.action_id = a.id
>> /* and l.event_tsc > i.event_date*/
>> where
>> l.event_tsc <
>> i.event_date+coalesce(i.event_time, cast('00:00' as time))+a.day_period
>>
>> Results on my powerful PC, windows (the server is much slower)
>>
>> Plan
>> ------------------------------------------------
>> PLAN JOIN (JOIN (A NATURAL, T INDEX (RDB$PRIMARY13), S INDEX
>> (RDB$FOREIGN17), C INDEX (RDB$PRIMARY9), I INDEX (RDB$FOREIGN15,
>> RDB$FOREIGN16)), L INDEX (RDB$FOREIGN13, RDB$FOREIGN2))
>>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>