Received: from lucas.mandanet.ch (lucas.mandanet.ch [195.15.14.8]) by hub.org (8.9.1/8.9.1) with SMTP id KAA22413 for ; Fri, 30 Oct 1998 10:31:55 -0500 (EST) (envelope-from jknobl@mandanet.ch) Received: from thomas.mandanet.ch (thomas.mandanet.ch [195.15.14.68]) by lucas.mandanet.ch (NTMail 3.02.13) with ESMTP id fa012563 for ; Fri, 30 Oct 1998 16:38:56 +0000 Message-ID: <3639DD8F.2CB99B94@mandanet.ch> Date: Fri, 30 Oct 1998 16:38:55 +0100 From: Jerome Knobl Organization: MandaNET X-Mailer: Mozilla 4.5b2 [en] (WinNT; I) X-Accept-Language: fr-CH MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: How can I optimize a research on text field? Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello, What index method can I use for a text field to have a better optimisation. I tried btree and hash method, but when I make a clause like : where nom like '%PICASSO%'; the request don't use any index!?! example: mayer98=> explain select count(*) from artistes, oeuvres where artistes.ref=oeuvres.refartiste and nom like '%PICASSO%'; NOTICE: QUERY PLAN: Aggregate (cost=4894.04 size=0 width=0) -> Nested Loop (cost=4894.04 size=874 width=8) -> Seq Scan on artistes (cost=4837.37 size=1 width=4) -> Index Scan on oeuvres (cost=56.67 size=685510 width=4) EXPLAIN Jérôme Knöbl