Backwards index scan
От | Dmitry Tkach |
---|---|
Тема | Backwards index scan |
Дата | |
Msg-id | 3F09B98F.8090805@openratings.com обсуждение исходный текст |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: