Hi again, Tom!

Tom Conlon wrote:

> Hi All,
>
> Generally speaking (so far) the strategy seems to give very good
> results. However, a strange anomaly has cropped up, can anyone throw
> some light on this one please?
>
> The following query was expected to use the available c.postcode index
> (IDXCONTPOSTCODE) but did not:
>
> SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
> c.available, c.email
> FROM cont c
> JOIN QryCriteria q1 on (q1.userid=409 and q1.CRITERIACOLUMN='POSTCODE' )
> WHERE c.postcode STARTING WITH q1.criteriavalue
>
> PLAN JOIN (C NATURAL,Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN))
> ---------------------------------------------------------
>
> whereas adding 'c.contid>0 and ' to start of WHERE clause forces the
> use of *postcode* index!
>
> SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
> c.available, c.email
> FROM cont c
> JOIN QryCriteria q1 on (q1.userid=409 and q1.CRITERIACOLUMN='POSTCODE' )
> WHERE c.contid>0 and c.postcode STARTING WITH q1.criteriavalue
>
> PLAN JOIN (Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),C INDEX
> (IDXCONTPOSTCODE,PK_CONT))
>
> Any ideas what causes this anyone?
>
> Tom

Ideas, yes. Good explanations that can be backed up by some kind of
certainty/insight, no. Here's my mix of knowledge and guesses, Dmitry,
Arno or someone will be able to confirm/deny.

As long as C is the first table in the plan, it cannot use any index
(since it is referring to another field). Somehow, the optimizer thinks
that the quickest way to do this, is to do a natural scan on C and go
through every single record and then look up Q1. It must mean that it
thinks that using the index wouldn't be too selective, but I don't know
why it thinks this.

As for changing the plan with a c.contid>0, this should be due to the
optimizer thinking this is a selective index (not knowing that the vast
majority have a positive value in this field), and once it uses that
index, combining it with the postcode index is not too much of an
additional task.

This is actually part of the reason for my answer that you disputed:

>> How many different values are there in CRITERIACOLUMN?
>> If there are only a handful,
>> NATURAL could be a better option than using an index.
>
> Is this really true? Surely the selectivity of the index would
> determine the wisdom of ignoring an index and performing a table scan
> or not?

My experience with Firebird, is that when it has the option to use a
very selective index and an index with rotten selectivity (in the actual
case in question, not indexes with rotten selectivity in general), then
it sometimes choose to use both indexes, with a big performance hit as a
result. In general, Firebird will choose a very good plan, but sometimes
it really fails.

This was the basis for my above answer, and Firebird may well be wise
enough to choose the best plan in your original question. Though the
example included:

>DELETE FROM QRYCRITERIA;
>INSERT INTO QRYCRITERIA VALUES ('TYPEID', 4 );
>ditto for 5,6,7,12,13,14,15
>INSERT INTO QRYCRITERIA VALUES ('AREAID', 1475 );
>ditto for 225,222

and when I saw the plan actually use the index for this small example
where you were interested in all rows of QRYCRITERIA, I thought that was
a bad choice. Though if QRYCRITERIA is filled with lots of other rows
and values, not applicable to your query, then I can understand the use
of these indexes.

I think there is one way to hint Firebird to use another plan in your
particular case - I've experienced something similar on one occasion,
though that was on a CHAR field containing only numbers:

SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
c.available, c.email
FROM cont c
JOIN QryCriteria q1 on (q1.userid=409 and q1.CRITERIACOLUMN='POSTCODE' )
WHERE c.postcode between q1.criteriavalue and q1.criteriavalue || 'ZZZZ'

(This requires that there is no postcode starting q1.criteriavalue which
comes after 'ZZZZ').

HTH,
Set
-happier to be a Firebird Foundation member than to put join criteria in
the where clause and vice versa
-www.firebirdsql.org/index.php?op=ffoundation