Re: Shouldn't the planner have a higher cost for reverse index scans?

Tom Lane wrote:
> Lists <lists(at)on-track(dot)ca> writes:
>
>> The query
>>
>
>
>> select comment_date
>> from user_comments
>> where user_comments.uid=1
>> order by comment_date desc limit 1
>>
>
>
>> Explain:
>> "Limit (cost=0.00..2699.07 rows=1 width=8) (actual
>> time=52848.785..52848.787 rows=1 loops=1)"
>> " -> Index Scan Backward using idx_user_comments_comment_date on
>> user_comments (cost=0.00..5789515.40 rows=2145 width=8) (actual
>> time=52848.781..52848.781 rows=1 loops=1)"
>> " Filter: (uid = 1)"
>> "Total runtime: 52848.840 ms"
>>
>
>
>> takes 10's of seconds to complete (52 sec last run). However
>>
>
>
>> select comment_date
>> from user_comments
>> where user_comments.uid=1
>> order by comment_date limit 1
>>
>
>
>> Explain:
>> "Limit (cost=0.00..2699.07 rows=1 width=8) (actual
>> time=70.402..70.403 rows=1 loops=1)"
>> " -> Index Scan using idx_user_comments_comment_date on
>> user_comments (cost=0.00..5789515.40 rows=2145 width=8) (actual
>> time=70.398..70.398 rows=1 loops=1)"
>> " Filter: (uid = 1)"
>> "Total runtime: 70.453 ms"
>>
>
>
>> takes well under 1 sec.
>>
>
> AFAICS this is pure chance --- it is based on when we happen to hit the
> first row with uid = 1 while scanning in forward or reverse comment_date
> order. Unless you have evidence that the number of rows skipped over
> is similar in both cases, there is no reason to suppose that this
> example bears on Josh's concern.
>
> As noted by Merlin, if you're willing to create another index to help
> this type of query, then a two-column index on (uid, comment_date) would
> be ideal.
>
> regards, tom lane
>

Thank you Tom and Merlin (and Grzegorz for the answer to my other
question I no longer need). The composite index seems to do the trick.
The reverse index scan is now taking about the same time.

Rows with uid=1 should be spread throughout the table but there should
be a larger amount earlier in the table (based on insert order).

I already had a separate index on uid

CREATE INDEX idx_user_comments_uid
ON user_comments
USING btree
(uid);

Under the circumstances, shouldn't a bitmap of those 2 indexes be far
faster than using just the date index (compared to the old plan, not the
new composite index). Why would the planner not choose that plan?