From: Andreas Kostyrka <andreas@mtg.co.at>
To: pgsql-performance@postgresql.org
Subject: Index not used, performance problem
Date: 29 Mar 2003 10:49:19 +0100
Message-ID: <1048931358.7539.11.camel@andi-lap> (raw)
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
Attachments:
signature.asc (application/pgp-signature, 188B) download
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: andreas@mtg.co.at
Subject: Re: Index not used, performance problem
In-Reply-To: <1048931358.7539.11.camel@andi-lap>
References: <1048931358.7539.11.camel@andi-lap>
* 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