From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Paul Smith <paullocal@pscs.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index not being used in sorting of simple table
Date: Fri, 04 May 2007 16:26:23 +0100
Message-ID: <463B509F.5070406@enterprisedb.com> (raw)
In-Reply-To: <VPOP32.5.0.20070504153630.092.309f.1.f0e0d0d3@lmail.pscs.co.uk>
References: <VPOP32.5.0.20070504153630.092.309f.1.f0e0d0d3@lmail.pscs.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, paullocal@pscs.co.uk
Subject: Re: Index not being used in sorting of simple table
In-Reply-To: <463B509F.5070406@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox