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

On Thu, Apr 16, 2009 at 2:02 AM, Lists <lists@on-track.ca> wrote:
>
> Right, because they do.  If you think otherwise, demonstrate it.
> (bonnie tests approximating a reverse seqscan are not relevant
> to the performance of indexscans.)
>
> Working on it.  I *think* I've seen this issue in the field, which is why I
> brought it up in the first place, but getting a good test case is, of
> course, difficult.
>
>
> I think I may be experiencing this situation now.
>
> The query
>
> select comment_date
>     from user_comments
>     where user_comments.uid=1
>     order by comment_date desc limit 1

try this:
create index comment_data_uid_idx on user_comments(uid, comment_date);

select * from user_comments where (uid, comment_date) < (1, high_date)
  order by uid desc, comment_date desc limit 1;

select * from user_comments where (uid, comment_date) > (1, low_date)
  order by uid, comment_date limit 1;

low_date and high_date are arbitrarily chosen to be lower and higher
than the lowest and highest dates found in the table, respectively.
You will be amazed how much faster this is than what you are doing
now.  You will not need to make an index for the 'desc' case.

for ranges, (give me some comments for user x from now back to particular time:
set enable_seqscan = false;
select * from user_comments where (uid, comment_date)
  between(1, time_of_interest) and (1, high_date)
  order by uid desc, comment_date desc;

enable_seqscan is required because the server will suddenly and
spectacularly switch to sequential scans because it can't use the non
leftmost portion of the index in range queries (this only mainly
matters when the left-most field is inselective and the comparison is
equal).

merlin