Re: index not used with subselect in where clause ?

Hello and thank you for your quick reply...

> > But when I try doing a 
> > 
> >   SELECT attr1 FROM table1 WHERE attr1 IN ()
> >                              AND ;
> > 
> > then the SELECT on table1 uses a sequential scan running 'endlessly'.
> 
> >From the FAQ:
> 
> 4.23) Why are my subqueries using IN so slow?

OOOPS, I confess that I didn't think of revisiting the source tree 
after installing the complete HTML docs. I don't think the FAQ is 
in there, is it? Maybe this would be helpful...

Anyway the solution from the FAQ doesn't seem to help me.
Maybe I wasn't clear enough on what I'm trying to do, so here is a 
'real world' example:


1. That's my situation now:

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432 
   zewtest_7.1 -c "explain select * from allmain where idn in (select distinct 
   dokids_as_int from allslwfull where wort_nouml_lower like 'gen%')"
   NOTICE:  QUERY PLAN:

   Seq Scan on allmain  (cost=0.00..69328.08 rows=19619 width=556)
     SubPlan
       ->  Materialize  (cost=3.45..3.45 rows=1 width=4)
             ->  Unique  (cost=3.45..3.45 rows=1 width=4)
                   ->  Sort  (cost=3.45..3.45 rows=1 width=4)
                         ->  Index Scan using allslwfull_low_idx on allslwfull 

   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN                         


2. FAQ 4.23 now seems to suggest something like 

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432 
   zewtest_7.1 -c "explain select * from allmain where exists (select distinct 
   dokids_as_int from allslwfull where dokids_as_int = idn and 
wort_nouml_lower
   like 'gen%')"
   NOTICE:  QUERY PLAN:

   Seq Scan on allmain  (cost=0.00..69328.08 rows=19619 width=556)
     SubPlan
       ->  Unique  (cost=3.45..3.46 rows=1 width=4)
             ->  Sort  (cost=3.45..3.45 rows=1 width=4)
                   ->  Index Scan using allslwfull_low_idx on allslwfull  
   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN                     

Doesn't seem to make much of a difference... ;-)


3. On the other hand:

   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select distinct dokids_as_int from allslwfull where
   wort_nouml_lower like 'gen%'"
   NOTICE:  QUERY PLAN:

   Unique  (cost=3.45..3.45 rows=1 width=4)
     ->  Sort  (cost=3.45..3.45 rows=1 width=4)
           ->  Index Scan using allslwfull_low_idx on allslwfull 
   (cost=0.00..3.44 rows=1 width=4)

   EXPLAIN
   finn@chatsubo:~ > /Projekte/ENDEBIT/packages/pgsql-Test/bin/psql -p 7432
   zewtest_7.1 -c "explain select * from allmain where idn in (677676, 34487,
   45353)"
   NOTICE:  QUERY PLAN:

   Index Scan using allmainidn_idx, allmainidn_idx, allmainidn_idx on allmain
   (cost=0.00..10.44 rows=1 width=556)

   EXPLAIN                         

Being far from knowlegeable in terms of database theory and looking just 
at the figures returned by EXPLAIN I wondered if it wouldn't be much 
faster to simply evaluate the inner query and hand the result over to 
the outer query instead of performing an expensive join behind the scenes...

Am I thinking too naively here? 


greetings...
Christian


-- 
"The sky above the port was the color of television,
 tuned to a dead channel."
                                         -- W.G. --