Received: from www.webcraft.ch (www.webcraft.ch [212.55.198.249]) by hub.org (8.9.3/8.9.3) with SMTP id KAA10302 for ; Sat, 27 Nov 1999 10:32:49 -0500 (EST) (envelope-from matt@webcraft.ch) Received: (qmail 15269 invoked from network); 27 Nov 1999 15:32:16 -0000 Received: from ws1.webcraft.ch (HELO webcraft.ch) (212.55.198.244) by www.webcraft.ch with SMTP; 27 Nov 1999 15:32:16 -0000 Message-ID: <383FF97F.8F408F3B@webcraft.ch> Date: Sat, 27 Nov 1999 16:32:15 +0100 From: Matthias Ackermann X-Mailer: Mozilla 4.5 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@hub.org Subject: Usage of index in "ORDER BY" operations Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I notice following behaviour: I have a table "adress" with 100'000 adresses with columns (last_name, first_name, adressline1, etc.) and an index last_name_idx on the column "last_name". The query "SELECT * FROM adress ORDER BY last_name LIMIT 20 OFFSET 0;" takes forever and "EXPLAIN" shows that the index on last_name is not being used. On the other hand "SELECT * FROM adress WHERE last_name > '' ORDER BY last_name LIMIT 20 OFFSET 0;" returns the result immediately and "EXPLAIN" shows that the index on last_name is being used. So it seems that inserting a WHERE-clause, even if it doesn't do anything at all (i.e. doesn't reduce the result-set), is necessary to force the DB to make use of the index. It even says in the FAQ under 4.9) "Indexes are not used for ORDER BY operations." So I was wondering: Am I doing something wrong here or is the lesson simply: "Include all attributes of an index in a where-clause if you want the indexes to be used"? Is there a better way to tell the DB to make use of the index? BTW: This seems to be true for indexes on multiple columns, i.e. if having an index on (last_name, first_name) the query had to be: SELECT * FROM adress WHERE last_name >'' AND first_name >'' ORDER BY last_name, first_name LIMIT 20 OFFSET 0; Omitting the where-clause again leads to a very slow query. I apologize if this has been discussed many times before ... Thanks for your help. Matt