X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8398252926 for ; Mon, 13 Jun 2005 11:34:44 -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 33754-10 for ; Mon, 13 Jun 2005 14:34:34 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.91]) by svr1.postgresql.org (Postfix) with ESMTP id 0745B52812 for ; Mon, 13 Jun 2005 11:34:33 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 13 Jun 2005 09:34:38 -0500 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.2 Date: Mon, 13 Jun 2005 09:34:33 -0500 From: "Kevin Grittner" To: , Cc: , Subject: Re: Index ot being used Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.044 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/290 X-Sequence-Number: 12927 It sure would be nice if the optimizer would consider that it had the leeway to add any column which was restricted to a single value to any point in the ORDER BY clause. Without that, the application programmer has to know what indexes are on the table, rather than being able to just worry about the set of data they want. Obviously, if a column can have only one value in the result set, adding to any point in the ORDER BY can't change anything but performance. That sure sounds like something which should fall within the scope of an optimizer. It really should be a DBA function to add or drop indexes to tune the performance of queries, without requiring application programmers to modify the queries for every DBA adjustment. (When you have a database with over 350 tables and thousands of queries, you really begin to appreciate the importance of this.) >>> Tom Lane 06/12/05 10:56 AM >>> Madison Kelly writes: > Here is my full query: > tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type > FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, > file_name ASC; > This is my index (which I guess is wrong): > tle-bu=> \d file_info_7_display_idx > Index "public.file_info_7_display_idx" > Column | Type > -----------------+---------------------- > file_type | character varying(2) > file_parent_dir | text > file_name | text > btree, for table "public.file_info_7" The index is fine, but you need to phrase the query as ... ORDER BY file_type, file_parent_dir, file_name; (Whether you use ASC or not doesn't matter.) Otherwise the planner won't make the connection to the sort ordering of the index. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match