Skip site navigation (1) Skip section navigation (2)

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

From: Lists <lists(at)on-track(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Shouldn't the planner have a higher cost for reverse index scans?
Date: 2009-04-16 15:52:32
Message-ID: 49E75440.9000906@on-track.ca (view raw or flat)
Thread:
Lists: pgsql-performance
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?

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-04-16 15:54:49
Subject: Re: Really dumb planner decision
Previous:From: Robert HaasDate: 2009-04-16 15:49:14
Subject: Re: Really dumb planner decision

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group