Re: Some Improvement

>> But I don't see the "Backwards index scan" you mentioned.
> Then we're not there yet.

> I hesitate to suggest that you throw "SET enable_sort TO off" and then
> "SET enable_sort TO on" around your query, because it's so ugly,
> but that might be the best short-term answer.

No, actually that's no short-term answer at all. It turns out that the
"bogus" cost estimate was perfectly correct, because what the planner
was actually generating was a plan for a backwards index scan over the
whole table, with restrictions applied after the fact :-(. Forcing it
to use that plan won't help.

I have corrected this silly oversight. Attached is the patch needed to
make backwards index scans work properly in 7.0.*.

regards, tom lane

*** src/backend/optimizer/path/indxpath.c.orig Sun Apr 16 00:41:01 2000
--- src/backend/optimizer/path/indxpath.c Thu Jul 13 01:49:51 2000
***************
*** 196,202 ****
useful_for_ordering(root, rel, index, ForwardScanDirection))
add_path(rel, (Path *)
create_index_path(root, rel, index,
! NIL,
ForwardScanDirection));
}

--- 196,202 ----
useful_for_ordering(root, rel, index, ForwardScanDirection))
add_path(rel, (Path *)
create_index_path(root, rel, index,
! restrictclauses,
ForwardScanDirection));
}

***************
*** 208,214 ****
if (useful_for_ordering(root, rel, index, BackwardScanDirection))
add_path(rel, (Path *)
create_index_path(root, rel, index,
! NIL,
BackwardScanDirection));

/*
--- 208,214 ----
if (useful_for_ordering(root, rel, index, BackwardScanDirection))
add_path(rel, (Path *)
create_index_path(root, rel, index,
! restrictclauses,
BackwardScanDirection));

/*