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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Lists <lists(at)on-track(dot)ca>
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 16:01:12
Message-ID: 19095.1239897672@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Lists <lists(at)on-track(dot)ca> 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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2009-04-16 16:04:40
Subject: Re: Really dumb planner decision
Previous:From: Matthew WakelingDate: 2009-04-16 15:54:49
Subject: Re: Really dumb planner decision

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