From: | Vadim Mikheev <vadim(at)krs(dot)ru> |
---|---|
To: | andreas(dot)fredriksson(at)pacer(dot)se |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] SubQuery speed |
Date: | 1998-09-09 02:29:07 |
Message-ID: | 35F5E7F3.9FE5925C@krs.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
andreas(dot)fredriksson(at)pacer(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Jennis | 1998-09-09 13:45:26 | Loading Array data from delimited ascii files |
Previous Message | Craig Orsinger | 1998-09-08 17:22:46 | Re: [SQL] Creating Indexes for MAC and IP Data Types |