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 [EMAIL PROTECTED]
