X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4F8F8475AE5 for ; Sat, 29 Mar 2003 11:57:55 -0500 (EST) Received: from smtp.inode.at (smtp-01.inode.at [62.99.194.3]) by postgresql.org (Postfix) with ESMTP id A56FD475A80 for ; Sat, 29 Mar 2003 11:57:54 -0500 (EST) Received: from line-c-227.adsl-dynamic.inode.at ([62.99.151.227]:35422 helo=andi-lap) by smtp.inode.at with smtp (Exim 4.10) id 18zJdR-0000wT-00; Sat, 29 Mar 2003 17:56:49 +0100 Received: by andi-lap (sSMTP sendmail emulation); Sat, 29 Mar 2003 17:57:58 +0100 Subject: Re: Index not used, performance problem From: Andreas Kostyrka To: Christopher Kings-Lynne Cc: pgsql-performance@postgresql.org In-Reply-To: <20030329214139.N4697-100000@houston.familyhealth.com.au> References: <20030329214139.N4697-100000@houston.familyhealth.com.au> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-YhVtnAnJHYTlkgVB/PLB" Organization: Message-Id: <1048957078.7539.17.camel@andi-lap> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 29 Mar 2003 17:57:58 +0100 X-Spam-Status: No, hits=-45.1 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,PGP_SIGNATURE_2, QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, 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/192 X-Sequence-Number: 1490 --=-YhVtnAnJHYTlkgVB/PLB Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Sat, 2003-03-29 at 14:47, Christopher Kings-Lynne wrote: > As for the LIKE searches, the only ones that PostgreSQL can index are of > the form 'FOO%', which is what you are doing. However, I believe that > PostgreSQL cannot do this if your database encoding is anything other than > 'C'. So, if you are using an Austrian encoding, it might not be able to > use the index. Well, I use LATIN1. How do I store 8-bit chars else? And if so, PostgreSQL seems quite strongly broken, because a relational database relies by design heavily on indexes. > Also, what is the point of searching for LIKE '%'? Why not just leave that > out? Well, it's about generating the SQL query. Actually it's just a border case for searching for a given prefix. Andreas --=-YhVtnAnJHYTlkgVB/PLB 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+hdCVHJdudm4KnO0RAnB7AJ4hE5NYgPJmYjiWhnMexvRmfumf9ACgwuah 2XmtElYM+nBzciXeT5fWEDc= =4NmH -----END PGP SIGNATURE----- --=-YhVtnAnJHYTlkgVB/PLB--