Re: Index not being used in sorting of simple table

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