DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=CG0O9+uBAxJBah68Yq6ppwSbbGkZLIIkT4TNw/lAoWDOwRXuAhJU/gbYC1CS4Il6C7ARXKs98QqA9HYH6uNuaABV9aEbD8dPgdFuBtqmf8dcNLKYfxWtR5FgpyKZrz+6wacFtJN4xyUbRysJ0TQWbza6rIdfFXFrLzfXY3WVAOA=
Message-ID: <463B509F.5070406@enterprisedb.com>
Date: Fri, 04 May 2007 16:26:23 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Paul Smith <paullocal@pscs.co.uk>
CC: pgsql-performance@postgresql.org
Subject: Re: Index not being used in sorting of simple table
References: <VPOP32.5.0.20070504153630.092.309f.1.f0e0d0d3@lmail.pscs.co.uk>
In-Reply-To: <VPOP32.5.0.20070504153630.092.309f.1.f0e0d0d3@lmail.pscs.co.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Paul Smith wrote:
> Why doesn't it use the other index? If use 'set enable_seqscan=0' then 
> it does.

Just a guess, but is the table clustered on column a? Maybe not 
explicitly, but was it loaded from data that was sorted by a?

Analyzer calculates the correlation between physical order and each 
column. The planner will favor index scans instead of sorting when the 
correlation is strong, and it thinks the data doesn't fit in memory. 
Otherwise an explicitly sort will result in less I/O and be therefore 
more favorable.

You can check the correlation stats with:
SELECT tablename, attname, correlation FROM pg_stats where tablename='x';

> I tried using EXPLAIN ANALYZE to see how long it actually took:
> - seq scan - 75 secs
> - index scan - 13 secs
> - seq scan - 77 secs

> (I tried the seq scan version after the index scan as well to see if 
> disk caching was a factor, but it doesn't look like it)

That won't flush the heap pages from cache...

How much memory do you have and how large is the table? I suspect that 
the planner thinks it doesn't fit in memory, and therefore favors the 
seqscan+sort plan which would require less random I/O, but in reality 
it's in cache and the index scan is faster because it doesn't need to 
sort. Have you set your effective_cache_size properly?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com