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 7896652819 for ; Mon, 13 Jun 2005 14:51:02 -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 87110-09 for ; Mon, 13 Jun 2005 17:50:55 +0000 (GMT) Received: from tomts13-srv.bellnexxia.net (tomts13-srv.bellnexxia.net [209.226.175.34]) by svr1.postgresql.org (Postfix) with ESMTP id 18DCE52814 for ; Mon, 13 Jun 2005 14:50:52 -0300 (ADT) Received: from [192.168.2.99] ([64.228.1.94]) by tomts13-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20050613175050.MKWU25800.tomts13-srv.bellnexxia.net@[192.168.2.99]>; Mon, 13 Jun 2005 13:50:50 -0400 Message-ID: <42ADC77B.7000007@alteeve.com> Date: Mon, 13 Jun 2005 13:50:51 -0400 From: Madison Kelly User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.8) Gecko/20050513 Debian/1.7.8-1 X-Accept-Language: en-us, en, ja MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: Bruno Wolff III , Tom Lane Subject: Re: Index ot being used 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> <20050613031317.GA18988@wolff.to> <42AD008D.4080600@alteeve.com> <20050613035346.GA20703@wolff.to> <42AD0B94.9090508@alteeve.com> <20050613132852.GA31142@wolff.to> In-Reply-To: <20050613132852.GA31142@wolff.to> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/309 X-Sequence-Number: 12946 Bruno Wolff III wrote: > I am guessing you mean 'file_type' instead of 'file_info'. > > To do this efficiently you want an index on (file_type, file_parent_dir, > file_name). Currently you only have an index on (file_parent_dir, file_name) > which won't help for this query. You also need to order by file_type > even though it will be constant for all of the returned rows in order > to help out the planner. This will allow an index scan over the desired > rows that returns them in the desired order. > > Please actually try this before changing anything else. If I follow then I tried it but still got the sequential scan. Here's the index and query (copied from the 'psql' shell): 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" tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual time=15523.767..15731.136 rows=25795 loops=1) Sort Key: file_type, file_parent_dir, file_name -> Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490 width=119) (actual time=0.132..2164.757 rows=25795 loops=1) Filter: ((file_type)::text = 'd'::text) Total runtime: 15884.188 ms (5 rows) If I follow all three 'ORDER BY...' items match the three columns in the index. Again, thanks! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-