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





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org