Date: Sun, 12 Jun 2005 12:37:32 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Madison Kelly <linux@alteeve.com>
Cc: Tobias Brox <tobias@nordicbet.com>,
	pgsql-performance@postgresql.org
Subject: Re: Index ot being used
Message-ID: <20050612173732.GA28838@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Madison Kelly <linux@alteeve.com>,
	Tobias Brox <tobias@nordicbet.com>, pgsql-performance@postgresql.org
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
	<20050610155535.GO8451@tobias.nordicbet.com>
	<42AC42CB.8050703@alteeve.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <42AC42CB.8050703@alteeve.com>
User-Agent: Mutt/1.5.6i

On Sun, Jun 12, 2005 at 10:12:27 -0400,
  Madison Kelly <linux@alteeve.com> wrote:
> Indexes:
>     "file_info_7_display_idx" btree (file_type, file_parent_dir, file_name)

>   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;
>                                                          QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------

This is a case where postgres's planner can't make a deduction needed for
it to realize that the index can be used. Try rewriting the query as:

SELECT file_name, file_parent_dir, file_type 
  FROM file_info_7 WHERE file_type='d'
  ORDER BY file_type ASC, file_parent_dir ASC, file_name ASC;