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