Postgres Professional
   

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

From: Lists
Subject: Re: Shouldn't the planner have a higher cost for reverse index scans?
Date: ,
Msg-id: 49E75440.9000906@on-track.ca
(view: Whole thread, Raw)
In response to: Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane)
Responses: Re: Shouldn't the planner have a higher cost for reverse index scans?  (Tom Lane)
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, )
Tom Lane wrote: 
Lists <> 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?

pgsql-performance by date:

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