From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas F. O'Connell <tfo@sitening.com>
Cc: PgSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Index Selection: ORDER BY vs. PRIMARY KEY
Date: Mon, 19 Sep 2005 23:05:41 -0400
Message-ID: <1648.1127185541@sss.pgh.pa.us> (raw)
In-Reply-To: <5DDEAB6E-36F9-422C-A44F-56E190B9FF55@sitening.com>
References: <5DDEAB6E-36F9-422C-A44F-56E190B9FF55@sitening.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tfo@sitening.com
Subject: Re: Index Selection: ORDER BY vs. PRIMARY KEY
In-Reply-To: <1648.1127185541@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox