pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (11+ messages)  latest in thread

Message-ID: <1048931358.7539.11.camel@andi-lap>
Permalink:  ../1048931358.7539.11.camel@andi-lap/
Also on:    postgresql.org/message-id/1048931358.7539.11.camel@andi-lap

 · 

reply

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