> But I don't see the "Backwards index scan" you mentioned.
Then we're not there yet. It looks like there may indeed be a bug
here. Trying it with a dummy table:
regression=# create table ff1 (f1 int, f2 char(14));
CREATE
regression=# create index ff1i on ff1(f1,f2);
CREATE
regression=# explain select * from ff1 where f1 = 3 and f2 between '4' and '5'
regression-# order by f1,f2;
NOTICE: QUERY PLAN:
Index Scan using ff1i on ff1 (cost=0.00..2.02 rows=1 width=16)
EXPLAIN
regression=# explain select * from ff1 where f1 = 3 and f2 between '4' and '5'
regression-# order by f1 desc,f2 desc;
NOTICE: QUERY PLAN:
Sort (cost=2.03..2.03 rows=1 width=16) -> Index Scan using ff1i on ff1 (cost=0.00..2.02 rows=1 width=16)
EXPLAIN
regression=# set enable_sort TO off;
SET VARIABLE
regression=# explain select * from ff1 where f1 = 3 and f2 between '4' and '5'
regression-# order by f1 desc, f2 desc;
NOTICE: QUERY PLAN:
Index Scan Backward using ff1i on ff1 (cost=0.00..67.50 rows=1 width=16)
EXPLAIN
So it knows how to generate an indexscan backwards plan, but it's not
choosing that because there's something wacko with the cost estimate.
Hmm. This works great for single-column indexes, I wonder what's wrong
with the multi-column case? Will start digging.
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.
>> Was there anything to the theory about LOCALE slowing down the sort?
> Well, I didn't intentionally compile LOCALE support. Just did the usual
> ./configure --with-max-backends=128 (or whatever)
> gmake
That shouldn't cause LOCALE to get compiled. I'm still at a loss why
6.5 would be faster for your original query. For sure it's not
generating a more intelligent plan...
regards, tom lane
