From: Kjeld Peters <kjeld.peters@profiling-company.de>
To: pgsql-performance@postgresql.org
Subject: Created Index is not used
Date: Thu, 23 Feb 2006 13:35:51 +0100
Message-ID: <43FDAC27.7050305@profiling-company.de> (raw)
Select and update statements are quite slow on a large table with more
than 600,000 rows. The table consists of 11 columns (nothing special).
The column "id" (int8) is primary key and has a btree index on it.
The following select statement takes nearly 500ms:
SELECT * FROM table WHERE id = 600000;
A prepending "EXPLAIN" to the statement reveals a seq scan:
EXPLAIN SELECT * FROM table WHERE id = 600000;
"Seq Scan on table (cost=0.00..15946.48 rows=2 width=74)"
" Filter: (id = 600000)"
I tried a full vacuum and a reindex, but had no effect. Why is
PostgreSQL not using the created index?
Or is there any other way to improve performance on this query?
The PostgreSQL installation is an out of the box installation with no
further optimization. The server is running SUSE Linux 9.1, kernel
2.6.4-52-smp. (Quad Xeon 2.8GHz, 1GB RAM)
SELECT version();
"PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
(SuSE Linux)"
Thanks for any hints,
Kjeld
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: kjeld.peters@profiling-company.de
Subject: Re: Created Index is not used
In-Reply-To: <43FDAC27.7050305@profiling-company.de>
* 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