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

Index not used, performance problem

From: Andreas Kostyrka <andreas(at)mtg(dot)co(dot)at>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index not used, performance problem
Date: 2003-03-29 09:49:19
Message-ID: 1048931358.7539.11.camel@andi-lap (view raw or flat)
Thread:
Lists: pgsql-performance
Hi!

I've got the following problem:
PostgreSQL 7.2.1-2 (Debian) on Duron/700MHz, 512MB, IDE hdd (laptop).

I've got a table that has 6400 rows, an index on the deleted, nachname,
vorname and hvvsnummer attributes, and my O-R wrapper generate queries
like this:

SELECT patient.id, patient.vorname, patient.nachname, patient.titel,
patient.geburtsdatum, patient.hvvsnummer, patient.geschlecht,
patient.adresse_id, patient.beruf, patient.kommentar, patient.cave,
patient.zusatzversicherung, patient.deleted FROM patient WHERE
((((patient.deleted = 'f') AND (patient.nachname LIKE 'K%')) AND
(patient.vorname LIKE '%')) AND (patient.hvvsnummer LIKE '%'))

This results in a SeqScan von patient. Even more curious is that simpler
queries like 

select * from patient where deleted='f'; OR:
select * from patient where nachname LIKE 'K%';

all result in SeqScan on patient.

I've "analyzed" and "reindex" the table already multiple times, and
still PostgreSQL insists upon not using any index.

TIA for any pointers,

Andreas

mpp2=# \d patient
                 Table "patient"
       Column       |     Type     |  Modifiers
--------------------+--------------+-------------
 id                 | integer      | not null
 vorname            | text         | not null
 nachname           | text         | not null
 titel              | text         |
 geburtsdatum       | date         |
 hvvsnummer         | text         |
 geschlecht         | character(1) |
 adresse_id         | integer      |
 beruf              | text         |
 kommentar          | text         |
 cave               | text         |
 zusatzversicherung | text         |
 deleted            | boolean      | default 'f'
Indexes: patient_deleted,
         patient_hvvsnummer,
         patient_nachname,
         patient_vorname
Primary key: patient_pkey
Check constraints: "patient_geschlecht" (((geschlecht = 'm'::bpchar) OR
(geschlecht = 'w'::bpchar)) OR (geschlecht = '?'::bpchar))
Triggers: RI_ConstraintTrigger_352787,
          RI_ConstraintTrigger_352789,
          RI_ConstraintTrigger_352801,
          RI_ConstraintTrigger_352803,
          RI_ConstraintTrigger_352815

mpp2=# select count(*) from patient;
 count
-------
  6406
(1 row)

mpp2=# explain SELECT * FROM patient WHERE (patient.nachname LIKE 'K%');
NOTICE:  QUERY PLAN:

Seq Scan on patient  (cost=0.00..173.07 rows=272 width=70)

EXPLAIN
mpp2=# explain SELECT * FROM patient WHERE NOT deleted;
NOTICE:  QUERY PLAN:

Seq Scan on patient  (cost=0.00..157.06 rows=6406 width=70)

EXPLAIN
mpp2=# explain SELECT * FROM patient WHERE deleted='f';
NOTICE:  QUERY PLAN:

Seq Scan on patient  (cost=0.00..173.07 rows=6406 width=70)

EXPLAIN


Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2003-03-29 13:47:51
Subject: Re: Index not used, performance problem
Previous:From: Jeremiah ElliottDate: 2003-03-28 20:58:48
Subject: Re: slow query - where not in

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