X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9C874474E4F for ; Sat, 29 Mar 2003 04:49:24 -0500 (EST) Received: from smtp.inode.at (smtp-01.inode.at [62.99.194.3]) by postgresql.org (Postfix) with ESMTP id 4D5D3474E42 for ; Sat, 29 Mar 2003 04:49:23 -0500 (EST) Received: from line-c-227.adsl-dynamic.inode.at ([62.99.151.227]:35123 helo=andi-lap) by smtp.inode.at with smtp (Exim 4.10) id 18zCwi-0005BU-00 for pgsql-performance@postgresql.org; Sat, 29 Mar 2003 10:48:17 +0100 Received: by andi-lap (sSMTP sendmail emulation); Sat, 29 Mar 2003 10:49:19 +0100 Subject: Index not used, performance problem From: Andreas Kostyrka To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-d6Iv6pE/ZRMcQ9VEKCxJ" Organization: Message-Id: <1048931358.7539.11.camel@andi-lap> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 29 Mar 2003 10:49:19 +0100 X-Spam-Status: No, hits=-19.2 required=5.0 tests=BAYES_01,PGP_SIGNATURE_2,USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/188 X-Sequence-Number: 1486 --=-d6Iv6pE/ZRMcQ9VEKCxJ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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 =3D '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=20 select * from patient where deleted=3D'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=3D# \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 =3D 'm'::bpchar) OR (geschlecht =3D 'w'::bpchar)) OR (geschlecht =3D '?'::bpchar)) Triggers: RI_ConstraintTrigger_352787, RI_ConstraintTrigger_352789, RI_ConstraintTrigger_352801, RI_ConstraintTrigger_352803, RI_ConstraintTrigger_352815 mpp2=3D# select count(*) from patient; count ------- 6406 (1 row) mpp2=3D# explain SELECT * FROM patient WHERE (patient.nachname LIKE 'K%'); NOTICE: QUERY PLAN: Seq Scan on patient (cost=3D0.00..173.07 rows=3D272 width=3D70) EXPLAIN mpp2=3D# explain SELECT * FROM patient WHERE NOT deleted; NOTICE: QUERY PLAN: Seq Scan on patient (cost=3D0.00..157.06 rows=3D6406 width=3D70) EXPLAIN mpp2=3D# explain SELECT * FROM patient WHERE deleted=3D'f'; NOTICE: QUERY PLAN: Seq Scan on patient (cost=3D0.00..173.07 rows=3D6406 width=3D70) EXPLAIN --=-d6Iv6pE/ZRMcQ9VEKCxJ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQA+hWweHJdudm4KnO0RAjaJAKCGChqjgKaA2sEHozzyGhFgjkYrOACgnKcR pIaNAHrqykZ1xEfnGd2OBo0= =l/Ao -----END PGP SIGNATURE----- --=-d6Iv6pE/ZRMcQ9VEKCxJ--