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