Backwards index scan

Dmitry Tkach <dmitry@openratings.com>

From: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-bugs@postgresql.org, pgsql-general@postgresql.org
Date: 2003-07-07T18:18:55Z
Lists: pgsql-bugs, pgsql-hackers
I am not sure if this is really a bug, but it certainly looks like one 
to me...

I have a table that looks something like this:

create table huge_table
(
    int x,
    int y
);
create index huge_table_idx on huge_table (x,y);

It contains about 80 million rows...
I am trying to get those rows that have a particular value for x, 
ordered by y in descending order (and I am looking to get just a few 
first ones), so I am running a query like:

declare mycursor cursor for select * from huge_table where x=10 order by 
x desc, y desc;
fetch 10 from mycursor;

this query takes 10 to 20 minutes!

This is because there are *lots* (a few million) of matches for x=10, 
and _bt_first () scans through them *all* sequentually to get to the 
last one.

Now, if I change the query to look like:

declare mycursor cursor for select * from huge_table where x > 9 and x < 
11 order by x desc, y desc;
(which is the same thing)
then fetch 10 from mycursor; returns right away (under a second), just 
as I expected.

I understand that with the generic approach to operators in postgres it 
is, probably, not very feasible to try and teach _bt_first () to handle 
this situation automatically (it would need to know how to get 
next/previous value for every indexable type)... I guess, that could be 
done by adding another kind of strategy to pg_amop for example...
Another way to work around this would be to allow ordering spec to be a 
part of CREATE INDEX (I know, that informix does that for example) - so 
that, I could do
create index huge_table_idx on huge_table (x, y desc);
... and then select * from huge_table where x=10 order x, y desc;
would not require a backwards scan to begin with.

Can something like this be done? What do you think?

Thanks!

Dima