|Subject||Re: gbak-restored database is Extremely Slow on queries in Firebird 2.0|
> If you can tell me a better place to post this problem, I'd be veryThe release notes document where questions should be asked. I am sure
> Unfortunately, I can't use Firebird 1.5.x series because there is none
> available for my 64 bit system as far as I know. I believe the 2.0 RC
> series is all I can use.
you have access to a 32 bit system to run some tests. The main reason
isn't to be nasty, it is to make sure the support list doesn't get
flooded with beta issues .
In any case, the second possibility I suggested appears to be the
case. In this case, it is most likely going to be something you can
duplicate in Firebird 1.5.
According to your experience, the following plan is ideal.
PLAN JOIN (MA ORDER IDX_MEMBER_ATTR8 INDEX (IDX_MEMBER_ATTR1), CO
INDEX (PK_COUNTRY), RE INDEX (PK_REGION), CI INDEX (PK_CITY), T INDEX
However after a restore, the optimiser decides
PLAN SORT (JOIN (RE NATURAL, MA INDEX (IDX_MEMBER_ATTR3,
IDX_MEMBER_ATTR1), CO INDEX (PK_COUNTRY), CI INDEX (PK_CITY), T INDEX
The plan is basically the steps the database engine takes to return
your query. In the top plan the engine is doing an indexed walk
following your order by clause. From their it is joining across to the
country, across to the region, across to the city and across to the
In the second plan however, it reads the region in stored order,
joining across to the MEMBER_ATTR table using the combined data of the
birthday and region id, and then follows a similar path.
I imagine changing the region line to something like:
JOIN REGION RE ON (MA.region_id+0 = RE.region_id)
will prevent starting at region.
I also imagine that the birthday index should be even more useful than
the indexed walk through the order by. You may want to also prevent
the order by index from being used.
ORDER BY MA.last_online+0 DESC