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 0AF9752864 for ; Mon, 13 Jun 2005 00:14:01 -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 84764-03 for ; Mon, 13 Jun 2005 03:13:55 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id A713952809 for ; Mon, 13 Jun 2005 00:13:52 -0300 (ADT) Received: (qmail 19119 invoked by uid 500); 13 Jun 2005 03:13:17 -0000 Date: Sun, 12 Jun 2005 22:13:17 -0500 From: Bruno Wolff III To: Madison Kelly , pgsql-performance@postgresql.org, Tom Lane Subject: Re: Index ot being used Message-ID: <20050613031317.GA18988@wolff.to> Mail-Followup-To: Bruno Wolff III , Madison Kelly , pgsql-performance@postgresql.org, Tom Lane References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com> <20050610155535.GO8451@tobias.nordicbet.com> <42AC42CB.8050703@alteeve.com> <24636.1118591783@sss.pgh.pa.us> <42ACBC95.9020404@alteeve.com> <20050613030001.GB17491@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050613030001.GB17491@wolff.to> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/269 X-Sequence-Number: 12906 On Sun, Jun 12, 2005 at 22:00:01 -0500, Bruno Wolff III wrote: > On Sun, Jun 12, 2005 at 18:52:05 -0400, > Madison Kelly wrote: > > > > After sending that email I kept plucking away and in the course of > > doing so decided that I didn't need to return the 'file_type' column. > > Other than that, it would see my query now matches what you two have > > recommended in the 'ORDER BY...' front but I still can't get an index > > search. > > No it doesn't. Even if you don't return file_type you still need it > in the order by clause if you want postgres to consider using your > index. I didn't notice that you had changed the index. The reason this index doesn't help is that you can't use it to select on records with the desired file_type. > > Is there some reason you didn't actually try out our suggestion, but are > now asking for more advice? > > > > > Here is the latest query and the new index: > > > > tle-bu=> \d file_info_7_display_idx; > > Index "public.file_info_7_display_idx" > > Column | Type > > -----------------+------ > > file_parent_dir | text > > file_name | text > > btree, for table "public.file_info_7" > > > > tle-bu=> EXPLAIN ANALYZE SELECT file_parent_dir, file_name, file_display > > FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, > > file_name ASC; > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq