Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BB901475F6A for ; Thu, 3 Oct 2002 23:14:40 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 0E522475A09 for ; Thu, 3 Oct 2002 23:14:39 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g943EhhR016872; Thu, 3 Oct 2002 23:14:43 -0400 (EDT) To: Vincent-Olivier Arsenault Cc: pgsql-sql@postgresql.org Subject: Re: order by x DESC, y ASC indexing problem In-reply-to: <3D9CF7EC.8080102@up4c.com> References: <3D9CF7EC.8080102@up4c.com> Comments: In-reply-to Vincent-Olivier Arsenault message dated "Thu, 03 Oct 2002 22:07:40 -0400" Date: Thu, 03 Oct 2002 23:14:43 -0400 Message-ID: <16871.1033701283@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/49 X-Sequence-Number: 9767 Vincent-Olivier Arsenault writes: > How to have the planner use an index in the case of a query like : > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; A normal index on (X,Y) is useless for this query, because neither scan direction in the index corresponds to the sort ordering you are asking for. In theory you could build a custom "reverse sort order" operator class for X's datatype, and then make an index using the reverse opclass for X and the normal opclass for Y. Or the other way round (normal sort order for X and reverse for Y). In practice, as Josh notes nearby, this is a waste of time for the query as given: whole-table sorts usually are better done by sorting not by indexscanning. If you are doing a partial scan like SELECT ... ORDER BY ... LIMIT some-small-number then it might be worth the trouble to set up a custom-order index. regards, tom lane