Hello, I already re-calculated the indexes. But as far as I understood is that as soon as the natural keyword is present the indexes are not used at all.
The question is when and how decides the generator of the "plan" that it connot use the indexes.... Matthias On Tue, Mar 3, 2020 at 1:13 PM 'Mathias Pannier (unitel)' [email protected] [firebird-support] <[email protected]> wrote: > > > Can You check the selectivity of the index? Perhaps You could recalculate > that selectivity: http://www.firebirdfaq.org/faq167/ > > > > *Von:* [email protected] [mailto: > [email protected]] > *Gesendet:* Dienstag, 3. März 2020 12:41 > *An:* [email protected] > *Betreff:* Re: [firebird-support] order by takes too long > > > > > > Hello, > > > > it seems the problem is related to the VIEW. This is what I also figured > out so far.. Now I read: > > > > "If you see a *NATURAL *plan going against a big table, you've found the > problem. If you have where clause or JOIN to that table, make sure you > have index defined on related fields. If you do have index, but it isn't > used, perhaps you have ascending index (default) and you need descending > (or vice versa). Or perhaps you just need to rebuild the index statistics > so that Firebird finds it usable. That can be done with SET STATISTICS sql > command. > > > If you use views with unions (you cannot index a view), I highly recommend > you use at least Firebird 2.0 as earlier versions don't use any indexes of > underlying tables when you use WHERE or JOIN with a view. If you can't use > Firebird 2.0, the only way to speed it up is to write a stored procedure > that takes value in WHERE clause as agrument." > > > > from http://www.firebirdfaq.org/faq13/ > > > > I am using firebird 2.1.7 and can confirm that the problematic table has > the keyowrd "NATURAL" in the plan analyzer. > > According to the text indexes should work within views with FB > 2.0. > > > > I also added indexes DESC and ASC for any field of the tables which is > used in where statements. > > > > Any idea how to proceed from here? > > > > greetings > > > > Matthias > > > > > > > > > > On Tue, Mar 3, 2020 at 7:32 AM liviuslivius [email protected] > [firebird-support] <[email protected]> wrote: > > > > Hi > > > > Without analysis i can only advice to create descending index. > > Create descending index ixd_mytable__timestamp on mytable(timestamp) > > > > Regards, > > Karol Bieniaszewski > > > > ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna > Geschaeftsfuehrung Klaus Richter, Olaf Meyer > Amtsgericht Stendal > HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778 > Deutsche Bank IBAN DE53 86070024 0 6143234 00 > Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82 > _____________________________________________________________________ > Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet > ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes > Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs- > äußerung ist die des Autors und stellt nicht notwendigerweise die > Ansicht oder Meinung von ub.unitel GmbH dar. > Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich > erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung, > Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt. > _____________________________________________________________________ > > >
