On Sep 19, 2005, at 10:05 PM, Tom Lane wrote:
"Thomas F. O'Connell" <[EMAIL PROTECTED]> 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)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
