From: Craig Ringer <ringerc@ringerc.id.au>
To: Ruslan A. Bondar <fsat@list.ru>
Cc: Silvio Brandani <silvio.brandani@tech.sdb.it>
Cc: pgsql-admin@postgresql.org
Subject: Re: database not using indexes
Date: Thu, 10 Nov 2011 10:40:58 +0800
Message-ID: <4EBB39BA.5090102@ringerc.id.au> (raw)
In-Reply-To: <20111109195838.237a4470@list.ru>
References: <4EBA9AAC.9030900@tech.sdb.it>
<20111109195838.237a4470@list.ru>
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
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-admin@postgresql.org
Cc: ringerc@ringerc.id.au, fsat@list.ru, silvio.brandani@tech.sdb.it
Subject: Re: database not using indexes
In-Reply-To: <4EBB39BA.5090102@ringerc.id.au>
* 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