--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 01:20 PM 15/06/2007, you wrote:
> >--- In firebird-support@yahoogroups.com, "Sean" <firebird_tmc@> wrote:
> > >
> > > Hi,
> > >
> > > I have a table of about 3 million records. The table A has many
> > > columns, among them are ID (Primary key, BigInt,

auto-increasing), and

> > > update_time (DateTime).
> > >
> > > An ascending query completes in about 1 second:
> > > select first 1000 * from A
> > > where update_time >= '13-JUN-2006 00:00:00'
> > > and update_time <= '13-JUN-2007 00:00:00'
> > > and id <=3000000
> > > and id >= 2000
> > >
> > > Adopted plan is: PLAN(A INDEX(PK_A))
> > >
> > > but a descending query returns after 2 minutes:
> > > select first 1000 * from A
> > > where update_time >= '13-JUN-2006 00:00:00'
> > > and update_time <= '13-JUN-2007 00:00:00'
> > > and id <= 3000000
> > > and id >= 2000
> > > order by id desc
> > >
> > > Adopted plan is: PLAN(A ORDER A_IDX_ID_DESC)
> > >
> > >
> > > Several relevant indexes of table A are:
> > > 1. PK_A (PRIMARY KEY INDEX) on column ID, ascending
> > > 2. A_IDX_ID_DESC on column ID, descending
> > > 3. A_IDX_UPDATE_TIME_ASC on column update_time, ascending
> > > 4. A_IDX_UPDATE_TIME_DESC on column update_time, descending
> > >
> > > Can anyone help to improve descending query?
> > >
> > > Thanks a lot!
> > > Sean
> > >
> >
> >Sorry, I wrote a wrong index name in the plan adopted by first query,
> >and first query is not ordered, and it's fast. The ascending ordered
> >query is fast too:
> >
> >select first 1000 * from A
> > where update_time >= '13-JUN-2006 00:00:00'
> > and update_time <= '13-JUN-2007 00:00:00'
> > and id <= 3000000
> > and id >= 2000
> > order by id desc
> >
> >Adopted plan is: PLAN(A ORDER PK_A)
>
> Looks like yet another typo. This isn't your "ascending query", is
> it? The DESC ordering clause should make the plan use
> > 2. A_IDX_ID_DESC on column ID, descending
>
> Anyway, it would be interesting to see the plan and performance you
> get if you use BETWEEN to avoid those GTE and LTE comparisons: I
> wonder whether the optimizer might then try to do something useful
> with one of those indexes on the timestamp fields, given the
> opportunity to do straight-out equality evaluation.
>
> select first 1000 * from A
> where update_time BETWEEN
> '13-JUN-2006 00:00:00' and '13-JUN-2007 00:00:00'
> and id BETWEEN 2000 AND 3000000
> order by id desc
>
> ./heLen
>

Unfortunately it took the same time.

Adopted plan was still: PLAN(A ORDER A_IDX_ID_DESC).

Why does the primary key (PK_A) work much better than the descending
index A_IDX_ID_DESC? I thought the indexes are equivalent.