X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 472DAD1C90F for ; Mon, 28 Jul 2003 05:45:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 66740-10 for ; Mon, 28 Jul 2003 02:45:02 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 05288D1C90E for ; Mon, 28 Jul 2003 02:45:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6S5jtQ3027210; Mon, 28 Jul 2003 01:45:55 -0400 (EDT) To: "David Witham" Cc: pgsql-sql@postgresql.org Subject: Re: Using a compound primary key In-reply-to: References: Comments: In-reply-to "David Witham" message dated "Mon, 28 Jul 2003 13:59:52 +1000" Date: Mon, 28 Jul 2003 01:45:54 -0400 Message-ID: <27209.1059371154@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/412 X-Sequence-Number: 14526 "David Witham" writes: > select * from tariff > where tariff_type = 'UIA' > and prefix in ('44','441','4412','44123','441234','4412345','44123456') > order by prefix desc limit 1; > The query doesn't use the primary key index as I might expect: Flip the index column order --- this should do what you want if prefix is the first column in the index. (Improving that is on the wish-list, but it seems to require a wholesale rethink of the way the planner searches for indexes matching OR-clauses.) You may also need to phrase the ORDER BY as "prefix desc, tariff_type desc" to make it perfectly clear to the planner that you don't need a separate sort step ... not quite sure whether that will be needed or not. regards, tom lane