Message-ID: <4EBB39BA.5090102@ringerc.id.au>
Date: Thu, 10 Nov 2011 10:40:58 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: "Ruslan A. Bondar" <fsat@list.ru>
CC: Silvio Brandani <silvio.brandani@tech.sdb.it>, pgsql-admin@postgresql.org
Subject: Re: database not using indexes
References: <4EBA9AAC.9030900@tech.sdb.it> <20111109195838.237a4470@list.ru>
In-Reply-To: <20111109195838.237a4470@list.ru>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 09/11/11 23:58, Ruslan A. Bondar wrote:
> 
> Why have you decided it isn't using indexes?
> If index exists - postgres will use it.

Actually that's not necessarily the case.

PostgreSQL will only use an index if (a) the index can be used for that
kind of query and (b) using the index will be faster than doing a
sequential scan.

If a query requires all the data in a table, PostgreSQL is quite likely
to do a sequential scan of the table, because it'll need to read every
block anyway. Reading just the table (without reading the index) in
order is much faster than reading the index then doing semi-random reads
of the table.

Additionally, not all indexes can be used for all operations. For
example, a LIKE query with a prefix wildcard eg "%FRED" cannot use a
btree index, so any btree index on the searched field will be ignored.

--
Craig Ringer