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

From: Tom Lane
Subject: Re: Shouldn't the planner have a higher cost for reverse index scans?
Date: ,
Msg-id: 19095.1239897672@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Shouldn't the planner have a higher cost for reverse index scans?  (Lists)
List: pgsql-performance

Tree view

Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
 Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
  Re: Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
   Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
    Re: Shouldn't the planner have a higher cost for reverse index scans?  (Josh Berkus, )
     Re: Shouldn't the planner have a higher cost for reverse index scans?  (Lists, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Grzegorz Jaśkiewicz, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Merlin Moncure, )
       Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
      Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
       Re: Shouldn't the planner have a higher cost for reverse index scans?  (Lists, )
        Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane, )
    Re: Shouldn't the planner have a higher cost for reverse index scans?  (Matthew Wakeling, )

Lists <> writes:
> 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?

It wouldn't produce sorted output; you'd have to read all the rows with
uid 1 and then sort them to find the lowest [highest] comment_date.
I'm sure the planner did consider that, but guessed that the other way
would win on average.  The fact that you have lots of rows with uid=1
would tend to push its cost estimates in that direction.  Unfortunately
it doesn't have any clue that the rows with uid=1 are concentrated in
older comment_dates, making the approach a loser for the highest-date
flavor of the problem.

            regards, tom lane


pgsql-performance by date:

From: Matthew Wakeling
Date:
Subject: Re: GiST index performance
From: Kris Jurka
Date:
Subject: No hash join across partitioned tables?