X-Original-To: pgsql-sql@postgresql.org Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id EDA364764B8 for ; Wed, 12 Mar 2003 17:01:06 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h2CM0wub029648; Wed, 12 Mar 2003 17:00:58 -0500 (EST) To: "Dan Langille" Cc: pgsql-sql@postgresql.org Subject: Re: extending ORDER BY make query much slower In-reply-to: <3E6F45D8.15284.5994716@localhost> References: <3E6F45D8.15284.5994716@localhost> Comments: In-reply-to "Dan Langille" message dated "Wed, 12 Mar 2003 14:36:08 -0500" Date: Wed, 12 Mar 2003 17:00:58 -0500 Message-ID: <29647.1047506458@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200303/318 X-Sequence-Number: 12357 "Dan Langille" writes: > I've found that adding another field to the ORDER BY clause, times go > through the roof. Why does that surprise you? The original query is using an index to achieve the required ordering, so it can give you the first 100 rows without bothering to compute the remainder. The modified query has to actually compute all the rows, and sort them, before it knows which are the first 100. If you had an index matching the second ORDER BY clause, you'd probably get a plan similar to the first case. regards, tom lane