| Subject | Re: [firebird-support] Re: My query plan does not use correct index |
|---|---|
| Author | Ann Harrison |
| Post date | 2011-08-18T12:59:50Z |
On Aug 18, 2011, at 8:08 AM, "chris.waldmann" <Christian.Waldmann@...> wrote:
If there is no match or if the query is complex, Firebird uses its normal algorithm, reading the indexes first to find potentially matching rows, combining the results of all indexes that can be used, then retrieving those rows and validating them against the snapshot of the database available to the transaction, then if necessary, sorting them. This means that if you want orders for a certain amount, in a specific month, from a specific customer, only those rows are retrieved, even though the search involves indexes on amount, date, and customer.
If there is an index that matches the sort key, Firebird uses "navigational" index access, meaning that it finds the first match on that sort key, retrieves the record, then checks to see if it matches the transaction's snapshot, and if so, whether it matches the other criteria. For queries that take the first 10 records from a huge set, navigational access is a good thing -
SELECT FIRST 10 ATOMS FROM UNIVERSE ORDER BY ELECTRON_COUNT would be very slow if it first selected all atoms and then sorted them.
However there are cases where navigational access is much slower than the normal two-step index access. Consider the case above where you want orders from a specific customer for a specific amount in a specific month, and assume you want them sorted by date. There's an index on date, so the query is going to return all the orders for that month and throw away those that come from other customers or have the wrong amount. So you retrieve many more records than you need, but you avoid the cheap in-memory sort.
Use the monitoring tables to determine how many pages each variant of your query reads and (I think) you'll see that the extra time is spent rattling the disk, not sorting records.
Good luck,
Ann
>That's not going to work. [I'm on vacation in Maine on a sailboat with limited connectivity and an ipad ... try not to feel too sorry for me ... and haven't parsed the original plan, but ...] Firebird uses indexes in two different ways, depending on whether there's an index that corresponds to the sort key, meaning that the index includes all the sort key fields in the same order and matches the ascending/descending rules. So if you have a multi-field sort, indexes on the individual fields won't be used to order the results. On the other hand, having indexes on the individual fields may make the query faster, even though the results must be sorted. The cost of retrieving records from disk is vastly greater than sorting them in memory. Here's more...
>
>> It seems that if i removed the "order by" clause, the query works very fast. So it must be that order by part that consumes all the performance. Any idea how to fix this?
>>
>> Regards,
>> Reynaldi
>>
> Have you tried with single field index in the requested sort order for each field in the order
If there is no match or if the query is complex, Firebird uses its normal algorithm, reading the indexes first to find potentially matching rows, combining the results of all indexes that can be used, then retrieving those rows and validating them against the snapshot of the database available to the transaction, then if necessary, sorting them. This means that if you want orders for a certain amount, in a specific month, from a specific customer, only those rows are retrieved, even though the search involves indexes on amount, date, and customer.
If there is an index that matches the sort key, Firebird uses "navigational" index access, meaning that it finds the first match on that sort key, retrieves the record, then checks to see if it matches the transaction's snapshot, and if so, whether it matches the other criteria. For queries that take the first 10 records from a huge set, navigational access is a good thing -
SELECT FIRST 10 ATOMS FROM UNIVERSE ORDER BY ELECTRON_COUNT would be very slow if it first selected all atoms and then sorted them.
However there are cases where navigational access is much slower than the normal two-step index access. Consider the case above where you want orders from a specific customer for a specific amount in a specific month, and assume you want them sorted by date. There's an index on date, so the query is going to return all the orders for that month and throw away those that come from other customers or have the wrong amount. So you retrieve many more records than you need, but you avoid the cheap in-memory sort.
Use the monitoring tables to determine how many pages each variant of your query reads and (I think) you'll see that the extra time is spent rattling the disk, not sorting records.
Good luck,
Ann
>