Since neither PATHNAME nor FILENAME can be NULL, your statement should be equivalent to

select
AllFile.*
from AllFile
left join excludedfile
on
excludedfile.pathname = allfile.pathname and
(excludedfile.filename = allfile.filename or
excludedfile.filename = '*')
where
allfile.filename = '1.TXT' and
excludedfile.pk_excludedfile is null

Does this query show the same 'slowdown' from 2.0.3 to 2.1.2? If not, well, you could always try something like:

with A as
(select af.* from AllFile af where af.filename = '1.TXT')
select a.*
from a
left join excludedfile ef
on
ef.pathname = a.pathname and
(ef.filename = a.filename or
ef.filename = '*')
where
ef.pk_excludedfile is null

I didn't know that Firebird behaved differently in cases like the one you supply, thanks for enlightening me!

And, Kjell, like you I write quite a few ad hoc queries, although almost exclusively in Firebird. The one case where Firebird has used a lot more time than I expected, was when I tried joining tables in a Firebird 1.5 database on a field that was very selective in both tables, but where more than 50% of the records where NULL. I expected NULL records to simply be ignored, but found that adding WHERE <JoinField> IS NOT NULL reduced the time of execution from more than 24 hours down to about one minute (well, I don't remember whether it was one minute or five seconds, just that the query returned the result quickly, rather than time out on our network). Except from this case, I don't share your experience with Firebird queries being slow - be it joined queries or correlated subqueries (though, of course, I sometimes change the query after inspecting the plan).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of ruan_h
Sent: 12. mai 2009 01:35
To: firebird-support@yahoogroups.com
Subject: [firebird-support] 2.1.2 uses a less optimised execution path than 2.0.3

Hi everyone

Had a query running much slower in 2.1.2 than in 2.0.3, below is a prototype of what's happening. Has anyone come across this? Does this (mis)behaviour of optimiser affect other patterns of queries?

/* Metadata */

RECREATE TABLE ALLFILE (
PK_ALLFILE INTEGER NOT NULL PRIMARY KEY,
PATHNAME CHAR(255) NOT NULL,
FILENAME CHAR(255) NOT NULL
);

--

RECREATE TABLE EXCLUDEDFILE (
PK_EXCLUDEDFILE INTEGER NOT NULL PRIMARY KEY,
PATHNAME CHAR(255) NOT NULL,
FILENAME CHAR(255) NOT NULL
);

CREATE INDEX EXCLUDEDFILE_IDX1 ON EXCLUDEDFILE (FILENAME);

/*Data*/

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (1, 'C:\TEMP\', '1.TXT');

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (2, 'C:\TEMP\', '2.TXT');

INSERT INTO ALLFILE (PK_ALLFILE, PATHNAME, FILENAME)
VALUES (3, 'C:\TEMP\', '3.TXT');

--

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (1, 'C:\ABC\', '*');

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (2, 'C:\CCC\', '*');

INSERT INTO EXCLUDEDFILE (PK_EXCLUDEDFILE, PATHNAME, FILENAME)
VALUES (3, 'C:\TEMP\', 'ABC.TXT');

COMMIT;

Query:

select
AllFile.*
from AllFile
where
allfile.filename = '1.TXT' and
not exists (
select 1
from excludedfile
where
excludedfile.pathname = allfile.pathname and
(excludedfile.filename = allfile.filename or
excludedfile.filename = '*')
)

Both 2.1.2. and 2.0.3 choose this plan:

PLAN (EXCLUDEDFILE INDEX (EXCLUDEDFILE_IDX1, EXCLUDEDFILE_IDX1))
PLAN (ALLFILE NATURAL)

2.0.3 does 3 natural reads on AllFile, and 2 index reads on ExcludedFile, because it applies allfile.filename = '1.TXT' first.

2.1.2 does 3 natural reads on AllFile, but also 6 index reads on ExcludedFile, because it applies allfile.filename = '1.TXT' last.