Thread

Help, how to get sub-select to look at index?

Chris Albertson <chris@topdog.pas1.logicon.com> — 1998-05-27T20:56:43Z

Hello,

The outer SELECT  in a stament that uses sub selects appears not
to ever make use of index files.  Here is an example

(1)  select * from foo where bar = 1;

     The above will use an index if one exists.  It is very fast
     even on my 3 million row table.

(2)  select * from foo where bar IN (SELECT 1)

     The above seems to always do a sequencial scan of the
     table  It is very slow on my 3 million row table.
     
My real code is more complex.  The inner select returns about 100
rows which triggers about 100 sequencial scans of my 3 million row
table.  I let it run for hours and I have never seen it finish.
(I use a 2000 block buffer chache and disabled fsync)

Is there a trick I could use?  Can you type cast a subselect?
I have both btree and hash indexes
built and did a fresh vacuum.  Still no luck.  Just thought I'd
ask before writting a libpg C program to simulate (2).

(Please cc a direct reply)

Thanks,
 
-- 
--Chris Albertson

  chris@topdog.logicon.com                Voice:  626-351-0089  X127
  Logicon RDA, Pasadena California          Fax:  626-351-0699