Skip site navigation (1) Skip section navigation (2)

Created Index is not used

From: Kjeld Peters <kjeld(dot)peters(at)profiling-company(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Created Index is not used
Date: 2006-02-23 12:35:51
Message-ID: 43FDAC27.7050305@profiling-company.de (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Markus SchaberDate: 2006-02-23 12:46:02
Subject: Re: Created Index is not used
Previous:From: Andreas PflugDate: 2006-02-23 10:21:26
Subject: Re: Joins and full index scans...mysql vs postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group