Re: Index Selection: ORDER BY vs. PRIMARY KEY

On Sep 19, 2005, at 10:05 PM, Tom Lane wrote:

> "Thomas F. O'Connell" <tfo@sitening.com> writes:
>
>> Clearly, if the index on the timestamp field is there, postgres wants
>> to use it for the ORDER BY, even though the performance is worse. How
>> is this preference made internally? If both indexes exist, will
>> postgres always prefer the index on an ordered column? If I need the
>> index on the timestamp field for other queries, is my best bet just
>> to increase sort_mem for this query?
>
> If you suppose that Postgres has a "preference" for one index over
> another, you're already fatally off track.  It's all about estimated
> costs.  In this case, the plan with h_action_ts_idx is preferred
> because
> it has a lower estimated cost (2196.30) than the other plan
> (17041.66).
> The way to think about this is not that Postgres "prefers" one index
> over another, but that the estimated costs aren't in line with
> reality.
>
> It looks from the plans that there are a number of estimation errors
> giving you trouble, but the one that seems most easily fixable is
> here:
>
>       ->  Index Scan using h_action_id_idx on history h
> (cost=0.00..13260.87 rows=3820 width=480) (actual time=0.184..0.195
> rows=3 loops=1)
>             Index Cond: (action_id = $constant_data::bigint)
>
> Estimating 3820 rows matching $constant_data when there are really
> only
> 3 is a pretty serious estimation error :-( ... certainly more than
> enough to explain a factor-of-100 error in the total estimated costs.
>
> How recently did you last ANALYZE the history file?  If the ANALYZE
> stats are up-to-date and it's still blowing the rowcount estimate by
> a factor of 1000, maybe you need to increase the statistics target for
> this column.
>
>             regards, tom lane

Thanks for the guidance, Tom. I don't know why I was "fatally off
track" on this one. It was indeed statistics related. pg_autovacuum
hadn't visited this table for a long enough window to have an impact
on the estimates. A sad case of the should've-known-betters...

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)