Hi Set

Thanks for the suggestion. As I said in another response to Helen, it's easy
to fix/improve this query and indeed I do think it should be improved. But
my focus here is to try to understand what makes the new *smarter* optimiser
choose a slower path than the old optimiser. Without solving this, I'm just
not confident enough that our production won't be affected by the upgrade
from 2.0 to 2.1.

Cheers
Huan

2009/5/12 Svein Erling Tysvær <svein.erling.tysvaer@...>

>
>
> 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<firebird-support%40yahoogroups.com>[mailto:
> firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>] On
> Behalf Of ruan_h
> Sent: 12. mai 2009 01:35
> To: firebird-support@yahoogroups.com <firebird-support%40yahoogroups.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.
>
>

[Non-text portions of this message have been removed]