Received: from dune.krs.ru ([195.161.16.38]) by hub.org (8.8.8/8.7.5) with ESMTP id WAA28551 for ; Tue, 8 Sep 1998 22:32:45 -0400 (EDT) Received: from krs.ru (localhost.krs.ru [127.0.0.1]) by dune.krs.ru (8.8.8/8.8.8) with ESMTP id KAA05038; Wed, 9 Sep 1998 10:29:07 +0800 (KRSS) (envelope-from vadim@krs.ru) Sender: root@dune.krs.ru Message-ID: <35F5E7F3.9FE5925C@krs.ru> Date: Wed, 09 Sep 1998 10:29:07 +0800 From: Vadim Mikheev Organization: OJSC Rostelecom (Krasnoyarsk) X-Mailer: Mozilla 4.05 [en] (X11; I; FreeBSD 2.2.6-RELEASE i386) MIME-Version: 1.0 To: andreas.fredriksson@pacer.se CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] SubQuery speed References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit andreas.fredriksson@pacer.se wrote: > > BEGIN; > DECLARE CURSOR curs FOR > SELECT url,title FROM idx_files WHERE id IN ( > SELECT file_id FROM idx_ref WHERE word_id IN ( > SELECT id FROM idx_words WHERE word = 'search term 1' OR .. ) ); > FETCH FORWARD 10 IN curs; > END; > > The idx_files table holds about 1.000 rows, the idx_words table holds > about 50.000 words, and the idx_ref is a massive 250.000+ > row cross-reference (file_id, word_id) which links the words and > pages together. > > A query like the above took several hours on my Pentium 200 at work before > I killed the stuck daemon. This is kind of strange since the innermost > subquery is only suppose to return a single id. :-( > > Are there any alternate methods of solving this? I don't think it's > possible to write this kind of query using a multi-JOIN? Try to rewrite query using EXISTS instead of IN and create indices. Vadim