On 10/6/2010 5:06 PM, Alec Swan wrote:

>
> I have a query which joins several tables and then sorts on two
> fields. Both fields have ascending indexes declared on them. It takes
> over 10 seconds to run this query. If I remove the second sort
> criterion, then the query runs 100 times faster. The query plans for
> both queries are shown below.

Slow ...

> PLAN SORT (JOIN (PHYSICAL_COPY INDEX (IDX_cF3Z9NMr/P8YkbENbAJVkA==),
> COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==),
> PROJECT_CODE_DESCRIPTOR INDEX (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT
> INDEX (PK_f3m9slJ+02gL6hFClhrZvg==), COPY INDEX
> (PK_ZM6SRonqR8AHSQuCISgvnQ==)))

Fast ...

> PLAN JOIN (PHYSICAL_COPY ORDER IDX_PHYSICAL_COPY_SIZE_ASC INDEX
> (IDX_cF3Z9NMr/P8YkbENbAJVkA==), COPY_CLASSIFICATION INDEX
> (IDX_soqMJd+Yux0RNvCbmE9rrg==), PROJECT_CODE_DESCRIPTOR INDEX
> (IDX_lEwvSCR+VZpQCfw5Duxo0A==), PROJECT INDEX
> (PK_f3m9slJ+02gL6hFClhrZvg==), COPY INDEX
> (PK_ZM6SRonqR8AHSQuCISgvnQ==))
>

The "SELECT FIRST 1000 *" is interesting. Do you know how
many rows the query would select without the "FIRST" clause?
If it's a lot more than 1000, then the problem is that
Firebird can't walk two indexes at once, so ordering by two
fields with separate indexes requires that it retrieve all
the qualifying rows, sort them, then throw out all the rows
it selected after the first 1000.

If you were selecting all the rows that qualify, then you'd
get better performance from the second plan because its faster
to read rows in storage order and sort them than it is to read
them in index order. But reading a lot of stuff you don't
need is a great waste of time, so when you're returning a
subset of the records you select, walking an index is faster.

If both ordering fields were in the same table, you could
use a compound index. But they aren't.

Good luck,

Ann