X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8F7FD7957 for ; Tue, 20 Sep 2005 00:05:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09305-01 for ; Tue, 20 Sep 2005 03:05:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 20A22D926B for ; Tue, 20 Sep 2005 00:05:42 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j8K35f2B001649; Mon, 19 Sep 2005 23:05:41 -0400 (EDT) To: "Thomas F. O'Connell" Cc: PgSQL Performance Subject: Re: Index Selection: ORDER BY vs. PRIMARY KEY In-reply-to: <5DDEAB6E-36F9-422C-A44F-56E190B9FF55@sitening.com> References: <5DDEAB6E-36F9-422C-A44F-56E190B9FF55@sitening.com> Comments: In-reply-to "Thomas F. O'Connell" message dated "Mon, 19 Sep 2005 18:00:33 -0500" Date: Mon, 19 Sep 2005 23:05:41 -0400 Message-ID: <1648.1127185541@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200509/207 X-Sequence-Number: 14515 "Thomas F. O'Connell" 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