Received: from mail3.desupernet.net (IDENT:qmailr@mail3.desupernet.net [204.249.184.38]) by hub.org (8.9.3/8.9.3) with SMTP id KAA35932 for ; Thu, 5 Aug 1999 10:23:42 -0400 (EDT) (envelope-from a222@redrose.net) Received: (qmail 3524 invoked from network); 5 Aug 1999 14:23:41 -0000 Received: from port8-4.ephrata.desupernet.net (HELO linux.redrose.net) (208.170.179.115) by mail3.desupernet.net with SMTP; 5 Aug 1999 14:23:41 -0000 Received: from redrose.net (win98.redrose.net [192.168.1.5]) by linux.redrose.net (8.8.7/8.8.7) with ESMTP id JAA20565 for ; Thu, 5 Aug 1999 09:31:24 -0400 Sender: patrick@linux.redrose.net Message-ID: <37A99D4F.610E6F7C@redrose.net> Date: Thu, 05 Aug 1999 10:18:55 -0400 From: Patrick Giagnocavo X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.9-19mdk i686) X-Accept-Language: en MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: [SQL] qurey plan and indices References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit MESZAROS Attila wrote: > > Hi, > > I've experienced a brutal speedup (order of 2) separateing the following > subquery and making it manually: > > explain select name,description > from descriptions > where in (select name > from descriptions > where description like '%Bankverbindung%'); > > Seq Scan on descriptions (cost=163.98 rows=3575 width=24) > SubPlan > -> Seq Scan on descriptions (cost=163.98 rows=2 width=12) > [I had no patient to wait the resuls...] > Correct me if I am wrong, however if using LIKE '%something%' (which means, the field contains 'something' somewhere in the field) there is never a chance to use the index you have created - a sequential table scan MUST be made, thus you have to read all 3575 rows to return the set. However, if you change your query so that you are looking for LIKE 'Bankverbindung%' then an index can be used becase you know what the first few characters are known. Hope this helps, Cordially Patrick Giagnocavo a222@redrose.net