| Subject | RE: [firebird-support] Re: SQL and PLAN |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2010-11-22T14:11:30Z |
> I would prefer this any day.Sure it has to do with selectivity, the optimizer has (at least) two options that it considers to be about equally good.
>
> PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26)))
>
> What seems strange to me is that the same Firebird server on different Databases chooses differently.
> Or not - this must have to be with the Selectivity of the indices on TRANSAKTIONER?
> AddingSure, as I said there are no histograms - the optimizer has no clue about what values this field contains. Adding that line makes the optimizer realize you're only interested in a very small range of dates (2 Nov - 1 Dec 2010 = 30 days), which the optimizer probably recognizes to be an awful lot more specific than any date between 2 Nov 2010 and infinity. In my work, I often have to hint the optimizer regarding dates, I know that we generally use dates from 1953 until today, but Fb 1.5 never understands that the index is useful if I specify a 1 year interval, but not if I use a 50 year interval. Without the histograms, there's no way for the optimizer to understand that.
>
> and transaktioner.Dato<='2010-12-01 23:59:59'
>
> to the where clauses also makes firebird pick what plan I like.
> INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR+0 = VARER.PLU_NRSorry, I thought it was a number, change to
>
> This actually did the job :)
>
> But - Doing a +0 on a varchar field. What happens if it contains anything but numbers?
> My table should only contain numbers, but I can't be sure.
INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR||'' = VARER.PLU_NR
>And - If my customer is not lying, this problem has comes forward the last few days, which means Firebird has startedI don't think your customer is lying this time (after all, he doesn't state that he didn't touch anything). Selectivity changes with changes to tables, normally not enough to affect the choices done by the optimizer, but sometimes it is. Your customer was just a bit unfortunate, now it is up to you to fix.
>to guess another way tan before.
HTH,
Set